Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

pacoli2013
Contributor

Calculate totals for previous months in a table

Hello,

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

5 Replies
MVP
MVP

Re: Calculate totals for previous months in a table

You can use Month(AddMonths(Today(), -1)) for previous month

pacoli2013
Contributor

Re: Calculate totals for previous months in a table

Hello Sunny,

thanks for your Support.

My expression for the current month for Budget was: Sum({$ < Year = {$(= max(Year)) }, Month = {"<= $(=max(Month))"}, Quarter = >  } BudgetAmount)

When I transform it into for the previous month:

= Sum({$ < Year = {$(= max(Year)) }, Month = Month(AddMonths(Today(), -1)), Quarter = >  } BudgetAmount)

I don't get any results

What do I overlook of wrong?

Regards Court

vishsaggi
Esteemed Contributor III

Re: Calculate totals for previous months in a table

Did you try adding curly braces in your set modifier for Month field like below

= Sum({$ < Year = {$(= max(Year)) }, Month = {"=Month(AddMonths(Today(), -1))"}, Quarter = > } BudgetAmount)

MVP
MVP

Re: Calculate totals for previous months in a table

More like this

=Sum({$<Year = {$(= max(Year)) }, Month = {"$(=Month(AddMonths(Today(), -1)))"}, Quarter>} BudgetAmount)

pacoli2013
Contributor

Re: Calculate totals for previous months in a table

Thank you both for your Support, For the moment my Problem is solved