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

[Anonymous]

Hi Greg,

If you have 1 in all cells you have to use sum.

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

First Sum -> Total female enrolments on last 3 years.

Second Sum -> Total enrolments on last 3 years.

Regards!!

[Anonymous]

This isn't working either.  It is only showing the proportion of females for the year the user has selected. Not the average over three years

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

Thanks

Greg

[giakoum]

then you need to aggr over year and then take the avg

See the definition for aggr function

[tresesco]

Have you checked if the set year range alone is working? Try the below expression and check if that works as expected

Sum({<Year={'>$(=Max(Year)-3)<=$(=Max(Year))'}>} head)

[Anonymous]

Hi Greg, could you attach one example of your data and expected output please?

Thanks!

[Anonymous]

Sorry.  Don't know how to do that.

Greg

[sunny_talwar]

Check these link out

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[Anonymous]

Hi Greg,

I have tried with an example data that I have created, try this:

=Avg({1<Year={'>$(=Max(Year)-3)<=$(=Max(Year))'}>} Aggr(Sum({1<Year={'>$(=Max(Year)-3)<=$(=Max(Year))'},source={'HESASTUD'},gender={'Female'}>} head),Year))

Regards!!

[Anonymous]

Thanks Manuel.  That returned proportions of over 1,000,000%.

I have attached a sample if that helps.

Thanks

Greg

[sunny_talwar]

It seems to be taking the three year average

From 2012 till 2015, no?