Avg for expressions

[#1]

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

[#2]

Maybe this set analysis constructor should be help you

Set Analysis Wizard for QlikView | qlikblog.at

[orital81]

Hi Racine62

Aggr function might work here;

Try This:

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

[#4]

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

[jerem1234]

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!

[#6]

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

[jerem1234 · Accepted solution]

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!

[#8]

This worked.  Thank you so much for this.

Regards,

Racine62