Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
ashokunbi
Contributor III
Contributor III

Total Average of Averages

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

26 Replies
sunny_talwar

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

ashokunbi
Contributor III
Contributor III
Author

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.

ashokunbi
Contributor III
Contributor III
Author

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?

tcullinane
Creator II
Creator II

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

sunny_talwar

Try this

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

sunny_talwar

Or this

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

sunny_talwar

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

ashokunbi
Contributor III
Contributor III
Author

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.

sunny_talwar

May be this

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

ashokunbi
Contributor III
Contributor III
Author

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