How do I set the average expression?

[#1]

I would like to set the GME average to change when I selected a different year.

Somehow, it seems like my data average is taking from both years.

Sheet(Program)

Please refer to Sheet (GME Level Avg Score) for reference to the GME Average

I am currently using this expression.

=Avg({1<[Rotation]>}Aggr(((SUM ({1<Data-={'NA'}>} ( Data))/

COUNT({1<Data-={"NA"}>}Rotation)))

, Questions,Rotation))

I have appended my qvw file for your reference.

Can anybody help? Thank you.

[cesaraccardi]

Hi Nazri

Try replacing the expression by this:

=

avg(Aggr(sum({$<Data-={'NA'}>} Data)/count({$<Data-={'NA'}>} Rotation),Questions,Rotation))

Kind Regards,

Cesar

[#3]

Hi Cesar,

Thank you for your help. I have tried using the expression you suggested. However, my GME average score is incorrect.

For Year 2013 it should be as the following scores in the snapshot.

If I were to click on the any of the programs in the ACAD year of 2013,  typically the programs score should change but the GME Average would remain.

The Red Line (GME Average) scores should be as the following scores as in my first snapshot.

Thanks for your help.

[cesaraccardi · Accepted solution]

Hi Nazri,

I think I understand the issue now. See updated expression below, it is ignoring the selections of the field Program, so if you have any other fields that should be ignored you will have to add them onto the set analysis expression. Also the Year is being 'linked' using the function P().

=

avg({$<Year=P(Year)>} Aggr(sum({$<Data-={'NA'},Program=>} Data)/count({$<Data-={'NA'},Program=>} Rotation),Questions,Rotation,Year))

Cheers,

Cesar

[#5]

Thank you Cesar. It works.

Appreciate you help.