Average of the past three years based on selection

[Anonymous]

What function/expression could I use to average the sum of something over the past three years (based on the user selecting a year)?

Thanks

Greg

[Anil_Babu_Samineni]

Perhaps This

Avg({$<Year = {"=$(AddYears(Year,-3))"}>}Sales)

[tresesco]

May be something like:

Avg({<Year={'>$(=Max(Year)-3)<=$(=Max(Year))'}>} Sales)

[#4]

Hi ,

I suggest you to create two variables,  one variable that holds the value of a year (two years back from the selected year) (For eg; if you select 2011 then the variable should hold 2009 ) and the other variable that holds the selected year value.

Then you can write your expression as follow:

sum({<year={">=$(vPrev2Year) <=$(vSelectedYear)"}>}sales)

if selected year is 2011 then vPrev2Year = 2009 .

Regards,

Balakrishnan.R

[sunny_talwar]

Is this in a chart where you have year as your dimension?

[Anonymous]

Hi Greg:

Avg({<Year={'>=$(=Max(Year)-2)'}>} Sales)

Regards!

[sunny_talwar]

Not such a good idea Manuel. What if we have data from 2000 till 2016 and user selects 2010. This will average everything from 2007 till 2016. You do need to have a less than equal to statement in your set expression, else you won't get the desired output

[Anonymous]

Sorry, I have forgotten max range, Avg({<Year={'>=$(=Max(Year)-2)<=$(=Max(Year))'}>} Sales),

and another option is specify the years one by one: Avg({<Year={'$(=Max(Year)-2)','$(=Max(Year)-1)','$(=Max(Year))'}>} Sales)

Regards!

[Anonymous]

Thanks Manuel.  I think this should work but I'm not using a sales field.  I am trying to calculate a three year average for the number of students enrolled on University by course.  I have an expression that tries to calculate the average proportion of females enrolled.  I have tried to incorporate your expression but I don't think I am doing it correctly -

Avg({<Year={'>$(=Max(Year)-3)<=$(=Max(Year))'}, gender ={'Female'}>} enrolments) /
Avg({<Year={'>$(=Max(Year)-3)<=$(=Max(Year))'}>} enrolments)

I don't think it is working because my enrolments field has the number 1 in each cell.  Therefore, when I use the average function the result is 1.  Do I need to use the SUM and Avg functions together?

Thanks for your help

Greg

[Anonymous]

No