topic Re: Total Average of Averages in QlikView

Total Average of Averages

ashokunbi — Wed, 25 Nov 2020 16:16:04 GMT

I have a pivot table with two dimensions. I have an expression that calculates average in a field, I found that using avg(Field) doesn't give me the correct subtotal/total by dimension (using show partial sums). I am using the expression:

avg(aggr(avg(Field), Dim1, Dim2))

I get the correct subtotal (Dim1) but the total (Dim2) is less than it should be.

Any help will be appreciated. Thanks

Re: Total Average of Averages

Shiva123 — Thu, 14 Sep 2017 20:39:39 GMT

try this sum(aggr(avg( field), dim1, dim2 .....))

Re: Total Average of Averages

ashokunbi — Thu, 14 Sep 2017 20:50:39 GMT

That gives me a total of the averages. I am looking for an average of the averages. For example your result gave me the total of my averages at 235.95 for dim1 what I want is that divided by the count of the dim1 (7) for a result of 33.71. Dim1 result is fine, it's dim2 total I'm not getting. It should be sum of subtotals for dim1 divided by count of dim 2.

Thanks for the suggestions.

Re: Total Average of Averages

Shiva123 — Thu, 14 Sep 2017 20:53:05 GMT

can you provide some sample data

Re: Total Average of Averages

ashokunbi — Thu, 14 Sep 2017 20:59:18 GMT

Dim2	Dim1	Avg TAT
A		22.80
B		31.11
C		7.98
D		5.36
E		1.38
F		7.72
G	1	6.50
G	2	21.74
G	3	5.50
G	4	147.73
G	5	32.83
G	6	16.72
G	7	4.93
G	Total	33.71	Correct
H		32.21
I		18.29
J		22.12
Total		23.15	Incorrect

Here you go

Re: Total Average of Averages

Shiva123 — Thu, 14 Sep 2017 21:28:42 GMT

try this AVG(AGGR(AVG(aggr(avg(FIELD),DIM1,DIM2)),DIM2))

Re: Total Average of Averages

ashokunbi — Thu, 14 Sep 2017 21:40:41 GMT

Getting close. Sum of subtotal averages is 182.68. Count of dim2 is 10. I should be getting total average of 18.27.

Thanks

Re: Total Average of Averages

Shiva123 — Thu, 14 Sep 2017 21:48:02 GMT

what is the answer are you getting now ?

Re: Total Average of Averages

ashokunbi — Fri, 15 Sep 2017 11:36:07 GMT

16.61

Re: Total Average of Averages

tcullinane — Fri, 15 Sep 2017 11:41:13 GMT

I would suggest use dimensionality to control what is displayed in the subtotal and total fields, eg

if (dimensionality()=1, aggr(avg()), avg())

Re: Total Average of Averages

sunny_talwar — Fri, 15 Sep 2017 11:44:03 GMT

Check two things

1) Is Sum(AGGR(AVG(aggr(avg(FIELD),DIM1,DIM2)),DIM2)) = 182.68

2) Count(DISTINCT Dim2) = 10

See which one is off here

Re: Total Average of Averages

ashokunbi — Fri, 15 Sep 2017 13:20:57 GMT

I got error in expression till I moved one of the brackets to look like this: if (dimensionality()=1, aggr(avg(), avg()))

The whole chart except the headers disappeared after I applied. So this didn't work but thanks for the help.

Re: Total Average of Averages

ashokunbi — Fri, 15 Sep 2017 13:32:03 GMT

Great detective work.

First one was 182.68, Second one was 11 not 10. I had marked the dimension to suppress when value is null but it looks like it's still counting the 1 value that was supposed to be suppressed otherwise Shiva's solution would have worked. Thanks for all the effort, all. Can you advise on how to eliminate factoring in the null data should not have been counted?

Re: Total Average of Averages

tcullinane — Fri, 15 Sep 2017 13:35:06 GMT

it wasnt supposed to be used exactly, just a way of using different calculations for subtotals and chart contents in, you'd need to look up dimensionality (and secondarydimensionality) to apply it.

As a hint i generally add a dimensionality expression to show what i need in the if statement

Re: Total Average of Averages

sunny_talwar — Fri, 15 Sep 2017 13:40:35 GMT

Try this

AVG({<Dim2 = {'*'}>}AGGR(AVG(aggr(avg(FIELD),DIM1,DIM2)),DIM2))

Re: Total Average of Averages

sunny_talwar — Fri, 15 Sep 2017 13:41:35 GMT

Or this

Avg(Aggr(Avg(Aggr(Avg({<Dim2 = {'*'}>}FIELD), DIM1, DIM2)), DIM2))

Re: Total Average of Averages

sunny_talwar — Fri, 15 Sep 2017 13:42:31 GMT

Wait Count(Dim2) should never count a null dimension.... are you using calculated dimension here?

Re: Total Average of Averages

ashokunbi — Fri, 15 Sep 2017 13:55:55 GMT

Neither worked. I should point out that there is actually an 11th value for Dim2 but it has 0 average in the field. The idea is to not count the 11th value and only count the 10 that has value in the field in coming up with the total average. Thanks.

Re: Total Average of Averages

sunny_talwar — Fri, 15 Sep 2017 14:03:53 GMT

May be this

Avg(Aggr(Avg(Aggr(Avg({<Dim2 = {"=Avg(FIELD) > 0"}>}FIELD), DIM1, DIM2)), DIM2))

Re: Total Average of Averages

ashokunbi — Fri, 15 Sep 2017 14:09:55 GMT

By Jove, that did it. You da man Sunny. Thanks a lot. Shout out to Shiva as well.