Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Since you are using Month as your dimension... use Before function to get previous month's data...
Before(Expression for This Month's Data)
Since you are using Month as your dimension... use Before function to get previous month's data...
Before(Expression for This Month's Data)
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
Hi Sunny,
Thank you for your help
Justin
Hi Juraj,
I has resolved the problem using Before() function
Thank you
Justin.
Hi Sunny,
Can you look my new post ?
Thanks for your help !
Kind regards,
Fred