topic Re: Average of percentages in QlikView

Average of percentages

wonkymeister — Mon, 09 Oct 2017 10:55:19 GMT

Morning,

i'm trying to understand how to calculate the average of a expression of percentages:

Training	Passed	Failed	Passed %
Fire Safety	200	48	81%
Data Protection	150	30	83%
First Aid	100	50	66%
Health & Safety	150	25	86%

below this pivot table i'd like to display the average % passed: Average % Passed Result = 79%

Passed expression :

count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)

Failed expression:

count({$<mandatory_or_optional = {'M'},passed = {'0'}>}1)

Passed % expression:

count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)/count({$<mandatory_or_optional = {'M'},passed = {'1','0'}>}1)

to get the average % do i have to wrap the % expression with Aggr using the Total function? I'm a bit lost with this!

cheers guys

Re: Average of percentages

tresB — Mon, 09 Oct 2017 11:01:29 GMT

If you want to show it in a text box, yes, you can try using aggr() and 'total'. Please let know (and perhaps share a sample qvw) if that doesn't work .

Re: Average of percentages

sunny_talwar — Mon, 09 Oct 2017 11:53:19 GMT

You should be able to use this and enable partial sum for your training dimension

Avg(Aggr(count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)/count({$<mandatory_or_optional = {'M'},passed = {'1','0'}>}1), Training))

Re: Average of percentages

swuehl — Mon, 09 Oct 2017 12:01:23 GMT

Depends on how you define your average.

It may be enough to just use

=count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)

/ count({$<mandatory_or_optional = {'M'},passed = {'1','0'}>}1)

i.e. the same expression for the grouped trainings and the total. This will consider the different number of exams in each training, while using the aggr() function, you ignore the different number of exams and just calculate the numeric average of the four percentages.

edit: Guess you are already decided by defining the expected result of 79%...

Re: Average of percentages

wonkymeister — Mon, 09 Oct 2017 12:04:58 GMT

Sunny, perfect, works a treat, thank you!