Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Avg for expressions


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

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Avg for expressions

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!

View solution in original post

7 Replies
Highlighted
Not applicable

Re: Avg for expressions

Maybe this set analysis constructor should be help you

Set Analysis Wizard for QlikView | qlikblog.at

Highlighted
Partner
Partner

Re: Avg for expressions

Hi Racine62

Aggr function might work here;


Try This:

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

Highlighted
Not applicable

Re: Avg for expressions

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

Highlighted
Specialist II
Specialist II

Re: Avg for expressions

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!

Highlighted
Not applicable

Re: Avg for expressions

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

Highlighted
Specialist II
Specialist II

Re: Avg for expressions

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!

View solution in original post

Highlighted
Not applicable

Re: Avg for expressions

This worked.  Thank you so much for this.

Regards,

Racine62