Open Forum

Expand all | Collapse all

Account Schedule - Total Error in NAV 2017

  • 1.  Account Schedule - Total Error in NAV 2017

    Posted Aug 29, 2019 05:47 PM
      |   view attached
    I have put a formula in Column 13 to add the months together. However, when I export the income statement with the "MONTH" Column Layout it does not give the correct total. It is summing January, and October through December in my Excel export.

    How do I make sure it correctly sums the entire year? I wanted to get the system to calculate so I don't have to add a Total column in Excel manually.

    Thanks everyone!!
    Account Schedules


    Lukas Sundahl

  • 2.  RE: Account Schedule - Total Error in NAV 2017

    Posted Aug 29, 2019 07:13 PM
    The Column No. field is an alphanumeric field, not a numeric field.  Therefore, it follows the NAV alphanumeric sorting rules.  The formula you entered of "1..12" is being interpreted as anything beginning with "1" and ending with "12".  So it will only pick up columns 1, 10, 11, and 12.  The character "2" comes after any value starting with a "1".

    There are a couple alternatives.  Use "A" thru "L" as the column numbers with the formula reading "A..L".  Or you could set the Column Type to "Net Change" and set the "Comparison Period Formula" to "FY[1..12]".  Both of these will work.

    David Wiser
    Project Manager
    Seattle WA


  • 3.  RE: Account Schedule - Total Error in NAV 2017

    Posted Aug 30, 2019 07:27 AM
    To expand on Dave's suggestion for formulas, you can even assign the same Column No. to all these columns and setup the formula as a single item. For instance, since you are simply summing all columns, set your Column No. to A for all Columns. In the Formula field for your total ​column, simply put A.

    Also, you do not need the [FYX] in the Formula field for the other columns.

    You can get lots of great information on @Kerry (Rosvold) Peters's Account Schedule blog here:

    Good luck!​

    Amanda Mayer
    New View Strategies
    Milwaukee WI


If you've found this thread useful, dive deeper into User Group community content by role