# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for
Did you mean:
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

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

1 Solution

Accepted Solutions
Specialist

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
MVP

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

Specialist

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.

Master

hi,

Try this,

DIM: Month

Exp: Count(Distinct ID)

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

Not applicable
Author

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