Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

justinphamvn
Contributor II

[Qlik Sense] Set Analysis Last Month in pivot table

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

Set Analysis Last Month.png

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

Thanks in advance.

Justin.

Tags (1)
1 Solution

Accepted Solutions

Re: [Qlik Sense] Set Analysis Last Month in pivot table

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

Before(Expression for This Month's Data)

4 Replies

Re: [Qlik Sense] Set Analysis Last Month in pivot table

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

Before(Expression for This Month's Data)

Luminary
Luminary

Re: [Qlik Sense] Set Analysis Last Month in pivot table

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

Re: [Qlik Sense] Set Analysis Last Month in pivot table

Hi Sunny,

Thank you for your help

Justin

justinphamvn
Contributor II

Re: [Qlik Sense] Set Analysis Last Month in pivot table

Hi Juraj,

I has resolved the problem using Before() function

Thank you

Justin.

Community Browser