Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Change over Previous Month

Hi,

I am trying to plot a monthly variance chart i.e current month variance over previous month.

Followed the approach described here

  http://aftersync.com/blog/the-magic-of-set-analysis-point-in-time-reporting

So the prior month count would be:

  Count({$<MonthID = {$(=Max(MonthID)-1)}, Year =,Month = >} distinct id)

So a tabular representation should look similar to the table below

Month        |  Count | Previous Month Count | Variance

Jan 2016          10                  0                            0

Feb 2016          15                10                          50%

March 2016      10                15                        -33%

But I am getting zeros in all for all previous count except for the second last month Feb 2016 in this case . I am guessing it because of Max(MonthID) -1 evaluates to Feb 2016.

Is there a way get the prior month count for all months? So that I can plot a graph similar to the one below; It could be a count difference

Clip39.png

1 Solution

Accepted Solutions
sebastiandperei
Valued Contributor

Re: Change over Previous Month

You could try to use Before function.

if your "Current" expression is Count(Distinct ID)

your "Before Month" expression would be Before(Count(Distinct ID))

and "Variance" would be Count(Distinct ID)/Before(Count(Distinct ID))-1

This will work if you have only Month (or Month/Year) Dimmention. If it doesn't work, try with Above, instead of Before.

4 Replies

Re: Change over Previous Month

Look at the approach specified here: The As-Of Table

Like you have pointed out that Month - 1 gives you Feb, it won't be able to give any other month's information since set analysis is evaluated once per table and not on each row. In order to overcome the problem, you need to create a LinkTable where you can link Previous Month's data to current Month.

Try the approach provided in the link above, but if you still have trouble, provide some sample data to help you better.

Best,

Sunny

sebastiandperei
Valued Contributor

Re: Change over Previous Month

You could try to use Before function.

if your "Current" expression is Count(Distinct ID)

your "Before Month" expression would be Before(Count(Distinct ID))

and "Variance" would be Count(Distinct ID)/Before(Count(Distinct ID))-1

This will work if you have only Month (or Month/Year) Dimmention. If it doesn't work, try with Above, instead of Before.

sasikanth
Valued Contributor III

Re: Change over Previous Month

hi,

Try this,

DIM: Month

Exp: Count(Distinct ID)

EXp: (Count(Distinct ID)- above(Count(DisitnctID)) / above(Count(Distinct ID))

Not applicable

Re: Change over Previous Month

Thanks guys Above(Count(Distinct ID) -1)  worked. I will Try the The As-Of Table approach as well.

Community Browser