topic Re: Expression: Averages using Pivot vs Straight table in QlikView

Expression: Averages using Pivot vs Straight table

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

I have an Avg expression in a straight table and pivot table: Avg(days). In the straight table I have the ability to select what type of expression totals I would like. So I select Expression totals: Avg. This gives me the Avg of the Avg for my total.

I really need to use a Pivot table, so I am trying to dupilcate the numbers from above. So, I tried avg(aggr(AVG(Days), PROGRAM, Division, REGION)) . This gives me a match to the above straight table for the totals but I'm now not getting a true average for my dimensions. I'm seeing an average of the averages, not an overall true avg like the straigh table. I hope this is clear. If so, is there a solution?

What I want to see.

Use a Pivot table. Receive a true average of my dimension and for the totals see an Average of an Average, like the straight table.

Re: Expression: Averages using Pivot vs Straight table

swuehl — Fri, 24 Jun 2016 15:18:53 GMT

Could you post which dimensions you are using in your chart and what you want to see at each dimensional level (e.g. using a screenshot of your two charts)?

Maybe try to use Dimensionality() to branch into different expressions:

=If(Dimensionality() = 0, avg(aggr(AVG(Days), PROGRAM, Division, REGION)), Avg(Days) )

Re: Expression: Averages using Pivot vs Straight table

sunny_talwar — Fri, 24 Jun 2016 16:27:20 GMT

I think it would be good to explore a without Dimensionality() solution first before going into that territory. So as Stefan mentioned, it would be good to know what all are your straight table dimensions?

Were you use PROGRAM, Division, REGION as dimensions and Avg(Days) as your expression? or are there more or less dimensions in the straight table? Also do you have the same dimensions in your pivot table? If possible provide a sample to further explore. If nothing works, then I think Dimensionality() should work as Stefan pointed out above

Re: Expression: Averages using Pivot vs Straight table

Anonymous — Fri, 24 Jun 2016 17:09:40 GMT

See attached for example. I'm trying to get my pivot table to match the straight table.

Re: Expression: Averages using Pivot vs Straight table

Anonymous — Fri, 24 Jun 2016 17:31:02 GMT

I have provided an example below. Thanks in advance!

Re: Expression: Averages using Pivot vs Straight table

sunny_talwar — Fri, 24 Jun 2016 17:31:44 GMT

Here is the dirty way:

If(Dimensionality() = 0, Avg(Aggr(AVG(Days), Reason)), Avg(Days))

Update:
May be Stefan can offer a more cleaner solution. I am unable to make this to work for right now