Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Expression: Averages using Pivot vs Straight table

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.

5 Replies
Highlighted
MVP
MVP

Re: Expression: Averages using Pivot vs Straight table

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) )

Highlighted

Re: Expression: Averages using Pivot vs Straight table

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

Highlighted
Creator III
Creator III

Re: Expression: Averages using Pivot vs Straight table

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

Highlighted
Creator III
Creator III

Re: Expression: Averages using Pivot vs Straight table

I have provided an example below. Thanks in advance!

Highlighted

Re: Expression: Averages using Pivot vs Straight table

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