Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master III
Master III

Try to add State 1

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

View solution in original post

16 Replies
sunny_talwar

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
Author

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
Author

Any other ideas?

sunny_talwar

Not unless you can share a sample to look at

jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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
Author

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

oknotsen
Master III
Master III

July = 7.

7 - 1 = 6.

6 = June.

May you live in interesting times!
Not applicable
Author

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.