Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Previous Month Reference Line

Hello,

I have a Gauge that shows AVG(Score), and I want to add a reference line that shows the Avg(Score) from the previous month.

I am selecting Month from a Date field in the table using Month(Date).

I have tried =AVG({<Month={$(=Max(Month(Date))-1)}>} [Score]) but this is not working and gives me the current month score.

Any ideas?

1 Solution

Accepted Solutions
antoniotiman
Not applicable

Re: Previous Month Reference Line

Try to add State 1

AVG({1<Date={'>=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))'}>} [Holiday Overall])

16 Replies
sunny_talwar
Not applicable

Re: Previous Month Reference Line

What is Month field format? Month(Date) is done in the script or front end filter object?

UPDATE: May be this:

=Avg({<Month={$(=Max(Month(Date))-1)}, Date>} [Score])

Not applicable

Re: Previous Month Reference Line

Hi Sunny,

That is again only giving me the same result as the current month. Both months have a different average.

The Month(Date) is a filter selection on the sheet.

Not applicable

Re: Previous Month Reference Line

Any other ideas?

sunny_talwar
Not applicable

Re: Previous Month Reference Line

Not unless you can share a sample to look at

jontydkpi
Not applicable

Re: Previous Month Reference Line

I think the problem is that you are doing a selection on an expression value (Month(Date)); in this case the selection is applied to the underlying field (Date), so override the Date selection in the set expression:

=AVG({<Month={$(=Max(Month(Date))-1)}, Date>} [Score])


But you probably need:

=AVG({<Month={$(=Month(Max(Date))-1)}, Date>} [Score])



Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Previous Month Reference Line

Hi Jonathan,

No that is still only giving me the current month as the reference line.

The page filter uses Month(Date) & ' ' & Year(Date) to create the selection list.

Not applicable

Re: Previous Month Reference Line

If I have July 2016 selected, and I use Max(Month(Date)-1 , it returns the value 6.

oknotsen
Not applicable

Re: Previous Month Reference Line

July = 7.

7 - 1 = 6.

6 = June.

May you live in interesting times!
Not applicable

Re: Previous Month Reference Line

Yes I know that.

That part of the code is giving the correct value, however when you combine it all together it gives the value for the current month not the previous month.