topic Avg for expressions in QlikView

Avg for expressions

Mon, 09 Dec 2013 12:38:42 GMT

I've the following expression to calculate a % from a string field:

({<PROGGRP = {'Progressed'}>}PROGGRP)/Count(PROGGRP)*100 labelled '% Progression'

I want to be able to create another expression that creates an overall average for '% Progression' by year(PROGYEAR) that stays constant as users drill down a chart. I can do this for the few fields that are numbers but Avg doesn't seem to accept expressions. Ideally I want to be able to have the following:-

Avg ({1} TOTAL <PROGYEAR> (Expression?))

Any help gratefully received,

Regards,

Racine62

Re: Avg for expressions

Mon, 09 Dec 2013 12:43:45 GMT

Maybe this set analysis constructor should be help you

Set Analysis Wizard for QlikView | qlikblog.at

Re: Avg for expressions

orital81 — Mon, 09 Dec 2013 12:47:10 GMT

Hi Racine62

Aggr function might work here;

Try This:

Avg(Aggr((Count({<PROGGRP = {'Progressed'}>}PROGGRP)/Count(PROGGRP)*100),PROGYEAR))

Re: Avg for expressions

Mon, 09 Dec 2013 13:14:38 GMT

Thanks for replying so quickly.

Unfortunately, I still have the problem that I want to keep this expression as a total average that remains constant as users drill down. If I adapt your expression:

Avg({1} TOTAL Aggr((Count({<PROGGRP = {'Progressed'}>}PROGGRP)/Count(PROGGRP)*100),PROGYEAR))

when I drill down it still changes.

Regards,

Racine62

Re: Avg for expressions

jerem1234 — Mon, 09 Dec 2013 13:35:21 GMT

You will need 1's in for your count functions as well. Try:

Avg({1} TOTAL Aggr((Count({1<PROGGRP = {'Progressed'}>}PROGGRP)/Count({1}PROGGRP)*100),PROGYEAR))

Hope this helps!

Re: Avg for expressions

Mon, 09 Dec 2013 13:45:26 GMT

Thanks for this. Your adaption does keep the average constant but it's constant for all years in PROGYEAR i.e. I want the overall average for each year to remain constant.

Regards,

Racine62

Re: Avg for expressions

jerem1234 — Mon, 09 Dec 2013 13:51:22 GMT

Try removing the total from your function:

Avg({1} Aggr((Count({1<PROGGRP = {'Progressed'}>}PROGGRP)/Count({1}PROGGRP)*100),PROGYEAR))

If this doesnt help, could you give a small example of how you want your data to look like?

Hope this helps!

Re: Avg for expressions

Mon, 09 Dec 2013 13:55:31 GMT

This worked. Thank you so much for this.

Regards,

Racine62