topic Re: Avg Function: Pivot vs Straight table in QlikView

Avg Function: Pivot vs Straight table

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

I have an Avg expression in both of my charts, a Pivot and Straight table. I'm having issues with the totals. I would like the total of the Pivot table to match the (correct total Avg) of the straight table. From the research I've done, it seems that the Pivot table does not have the option to create totals, just partial totals. Is there a way that I can adjust my expression to get a final total that matches each table?

=avg(Avg_Person_days)

Re: Avg Function: Pivot vs Straight table

effinty2112 — Tue, 21 Jun 2016 15:46:02 GMT

Hi MrJohn,

The best thing to do in your pivot table is to get an expression that sums to the total and divide by one that gives the count. The trick would be the use of the TOTAL qualifier in the second expression.

Use TOTAL and where necessary use a list of field names too that will tell the expression which dimensions to respect, otherwise TOTAL will disregard all the chart dimensions.

You'll have something like

Sum( ....) / Count(TOTAL <field1, field2,...> ...)

If you post some data I or someone else will make it clear.

Cheers

Andrew

Re: Avg Function: Pivot vs Straight table

Anonymous — Tue, 21 Jun 2016 15:57:19 GMT

Thanks, yes an example would be great. See below for a list of dimensions.

Pivot Dimensions:

Region

Division

Program

Expression: Avg(Avg_Days)

Something like this!

Sum(Avg_FirstVisit_Days)/Count(TOTAL <Division, REGION, PROGRAM>Avg_FirstVisit_Days)

Re: Avg Function: Pivot vs Straight table

effinty2112 — Tue, 21 Jun 2016 16:21:59 GMT

Hi,

Avg_Days seems an odd name for a field -especially one you trying to find an average of.

We could use an expression like

Sum(Avg_Days) / Count(something)

I don't know what a good denominator would be without knowing more about your data.

For example if I wanted to know the average size of some orders I would use an expression like

Sum([Order Value]) / Count(OrderID)

OrderID would not be a dimension in my chart. For your example I need to know what you mean by average.

Sum of what / count of what.

Cheers

Andrew

Re: Avg Function: Pivot vs Straight table

Anonymous — Tue, 21 Jun 2016 16:43:56 GMT

Thanks for the feedback. I want to make sure I'm explaining myself correctly, see attached. My pivot table and straight table averages match each other, it's the overall totals that differ. Why is that?