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

Moving annual average

Hi!

I am trying to create a moving annual average chart. I am trying to use set analysis for this but it doesn't give any data. This is the syntax that I use:

Sum({$<YearMonthNum ={">=$(=Date(addmonths(MakeDate(Mid(YearMonthNum,1,4),Mid(YearMonthNum,5,6)),-11),'YYYYMM')<=$(=Max(Date(MakeDate(Mid(YearMonthNum,1,4),Mid(YearMonthNum,5,6)),'YYYYMM'))"} >} amount)

Many thanks in advance!

7 Replies
Not applicable
Author

Hi,

May I ask if you have a date field? What is the format of YearMonthNum field?

Regards,

Janzen

Not applicable
Author

Hi!

I have a date field and the number format of YearMonthNum is integer.

Thanks!

Not applicable
Author

Hi,

You could try this one.

Sum({$<YearMonthNum = {">=$(=Date(addmonths(MakeDate(Mid(YearMonthNum,1,4),Mid(YearMonthNum,5,2)),-11),'YYYYMM'))<=$(=Max(Date(MakeDate(Mid(YearMonthNum,1,4),Mid(YearMonthNum,5,2)),'YYYYMM')))"} >} amount)

Hope to hear from you

Regards,

Janzen

Not applicable
Author

Thanks for your reply 🙂 I'm now able to see figures in the graph 11 months back plus the current period but the chart only displays the value for the period and not the sum of the range. If I select 201204 the value displayed in the chart is only from that period and not a sum of that period + 11 months back.  Is there a way to solve this so that I can see sum of the range?

Not applicable
Author

Hi,

Based on the expression above, the sum of amount is based on range from maximum date up to 11 months backward. What is your expression? Do you have a separated month field and year field?

Regards,

Janzen

Not applicable
Author

Hi!

I used the set analysis that you corrected as my expression. I do have a field called Month and Year but YearMonthNum is what I use.

Not applicable
Author

Hi,

Maybe you could try this one.

Sum({$<Year = {"<=$(=max(Year))"},

Month = {"<=$(=max(Month))"},

Month =,

Year =

>} amount)

Regards,

Janzen