Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jacek_stypulkow
New 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
MVP
MVP

Re: value difference between random months

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
New Contributor

Re: value difference between random months

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

MVP
MVP

Re: value difference between random months

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))

Community Browser