Moving annual average

[#1]

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!

[#2]

Hi,

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

Regards,

Janzen

[#3]

Hi!

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

Thanks!

[#4]

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

[#5]

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?

[#6]

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

[#7]

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.

[#8]

Hi,

Maybe you could try this one.

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

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

Month =,

Year =

>} amount)

Regards,

Janzen