[Qlik Sense] Set Analysis Last Month in pivot table

[justinphamvn]

Hi guys,

I have a problem with Set Analysis Last Month in pivot table

My data:  TransDate, Product Code, Quantity

I'd like compare 2 value :  This Month & Last Month  Quantity by Product Code ( Examples : Feb-2017 Vs. Jan-2017)

Then I try Measure Last Month

Set Analysis Last Month :

sum({<[TransDate.autoCalendar.Year]=
,[TransDate.autoCalendar.YearMonth]=
,[TransDate.autoCalendar.Date]={">=$(=MonthStart(Max([TransDate.autoCalendar.Date]),-1)) <=$(=MonthEnd(Max([TransDate.autoCalendar.Date]),-1))"}>} Quantity)

if I compare Current Month and Last Month it's correct but All month It's not working

Please let me know how to do this in Qlik Sense.

Thanks in advance.

Justin.

[sunny_talwar · Accepted solution]

Since you are using Month as your dimension... use Before function to get previous month's data...

Before(Expression for This Month's Data)

[juraj_misina]

Hello Justin,

When you use month as a dimension you are not able to display last month's value in current month's column simply because set analysis is ment to define a dataset considered for chart calculation. It is evaluated before the chart is rendered only to establish which data will be used for that particular chart and which will not. Only after evaluating the set analysis the data is aggregated based on dimensions. The reason why the result of your measure is zero in some months is that the intersection of that month's data and your set analysis is empty data set, therefore nothing to aggregate => 0.

But don't worry, there are ways how to achieve your goal. One is suggested by stalwar1‌ in previous comment (using Before() function), which will not work on the first column, the other is building As-Of calendar table as described here: The As-Of Table

Hope this helps.

Juraj

[justinphamvn]

Hi Sunny,

Thank you for your help

Justin

[justinphamvn]

Hi Juraj,

I has resolved the problem using Before() function

Thank you

Justin.

[fredericlehner]

Hi Sunny,

Can you look my new post ?

https://community.qlik.com/t5/New-to-Qlik-Sense/Pivot-Table-periods-PM1-YTD-PM12-for-each-year/td-p/...

Thanks for your help !

Kind regards,

Fred