Solved: Average of the past three years based on selection - Qlik Community

Anonymous · ‎2016-09-26

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

sunny_talwar · ‎2016-09-27

I guess I understand your issue, you need to ignore selection in academic year field

Sum({<Year={">$(=max(Year)-3)<=$(=Max(Year))"}, source={'HESASTUD'},gender ={'Male'}, academic_year>} head) /

Sum({<Year={">$(=Max(Year)-3)<=$(=Max(Year))"}, source={'HESASTUD'}, academic_year>} head)

View solution in original post

Anil_Babu_Samineni · ‎2016-09-26

Perhaps This

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

tresesco · ‎2016-09-26

May be something like:

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

Report Inappropriate Content · ‎2016-09-26

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 · ‎2016-09-26

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

Anonymous · ‎2016-09-26

Hi Greg:

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

Regards!

sunny_talwar · ‎2016-09-26

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 · ‎2016-09-26

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 · ‎2016-09-27

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 · ‎2016-09-27

No