topic Re: How do I set the average expression? in QlikView

How do I set the average expression?

Tue, 13 Jan 2015 05:52:04 GMT

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.

Re: How do I set the average expression?

cesaraccardi — Tue, 13 Jan 2015 15:18:46 GMT

Hi Nazri

Try replacing the expression by this:

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

Kind Regards,

Cesar

Re: How do I set the average expression?

Wed, 14 Jan 2015 00:44:26 GMT

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.

Re: How do I set the average expression?

cesaraccardi — Wed, 14 Jan 2015 13:58:16 GMT

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

Re: How do I set the average expression?

Thu, 15 Jan 2015 06:24:10 GMT

Thank you Cesar. It works.

Appreciate you help.