Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jacek_stypulkow
Contributor
Contributor

value difference between random months

Hi,

hope you can help in the below.

I need to find turnover deviations for every month in comparison with previous month.

The following expression works for one option - last month versus second to last month.

Sum({<[Posting date.autoCalendar.Date]

={'>=$(=AddMonths( MonthStart (Today()),-1))<=$(=AddMonths( MonthEnd (Today()),-1))'}>} Turnover)

-(Sum({<[Posting date.autoCalendar.Date]

={'>=$(=AddMonths( MonthStart (Today()),-2))<=$(=AddMonths( MonthEnd (Today()),-2))'}>} Turnover)),

How to modify the expression to make it working for random months selection, e.g.:

- select June to get the difference between June and May,

- select March to get the difference between March - February, etc

Appreciate any advice

Many thanks

Jacek

3 Replies
sunny_talwar

May be this

Sum({<[Posting date.autoCalendar.Date]

={'>=$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-1))<=$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-1))'}>} Turnover)

-(Sum({<[Posting date.autoCalendar.Date]

={'>=$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-2))<=$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-2))'}>} Turnover)),

jacek_stypulkow
Contributor
Contributor
Author

Thank you for quick reply Sunny, but it doesn't work, the expression shows null.

sunny_talwar

How about this

Sum({<[Posting date.autoCalendar.Date]

={'>=$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-1))<=$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-1))'}, Month, Year>} Turnover)

-(Sum({<[Posting date.autoCalendar.Date]

={'>=$(=AddMonths( MonthStart (Max([Posting date.autoCalendar.Date])),-2))<=$(=AddMonths( MonthEnd (Max([Posting date.autoCalendar.Date])),-2))'}, Month, Year>} Turnover))