5 Replies Latest reply: Apr 18, 2018 11:10 AM by Court van de Lisdonk RSS

    Calculate totals for previous months in a table

    Court van de Lisdonk



      I could need some help. For N-Printing I’m creating a document with some tables and graphics.


      For my tables I have for different dimensions: Countries, Productgroups, Division and Markettypes.


      For every dimension I will create a table, which will be imported in N-Printing.


      I have some problems with the expressions.


      Every table should have columns for:

      1.    Totals for Actual 2017 ((= current year -/- 1)

      2.    Growth Actual 2017 in % (= (Actual 2017 -/- Actual 2016) / Actual 2016

      3.    Total Budget 2018 (= current year)

      4.    Growth Budget 2018 in % (= (Budget 2018 -/- Budget 2017) / Budget 2017

      5.    Actual in actual month

      6.    Actual in previous month ( = actual month -/- 1)

      7.    Actual in month before previous month ( = actual month -/- 2)

      8.    Budget in actual month

      9.    Budget in previous month ( = actual month -/- 1)

      10.  Budget in month before previous month ( = actual month -/- 2)


      The expressions should not react by setting the filters (like year, month, MonthShortName etc.)

      I have added an example of a table where I have 2 dimensions Markettypes and Countries. The example has already the first four columns but I don’t know if the expressions are correct.

      Columns 8, 9, and 10 will have the same expressions with the only difference that the field for the amount will be different

      5, 6, and 7 will have ActualAmount

      8, 9, and 10 will have BudgetAmount

      I had the idea to use month(Today()) for current month and month(Today()-1) in the expressions but then I think I will have problems next year in January and February, because month will be -1 and 0.

      I hope someone can help me, of course you can change the expressions in the example

      Thanks in advance

      Regards Court