Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fvalente
New Contributor II

Pivot table understanding indent rows aggregation with average - help

Hello guys,

I am using the Pivot Table object to aggregate some data in the latest version of Qlik Sense enterprise version.

Data is being aggregated by average.

The problem is that I cannot understand how qlik is showing the indent Rows

As per below pic:

I have a total avg of my sample set as 6.6.

This sample has 2 subsamples (DE and DE Mobile) which have 8 and 5.67 as average respectively.

pivot count.png

the pivot above is derived from a small sample of 6 rows.

Full sample extract from excel below.

De Mobile has 4 rows and adds up to 22 as per below I would expect to have an average of 5.5 instead of 5.67.

pivot count excel.png

Furthermore, the total average is set in sens at 6.6 but would expect to see 6.3.

The 6.6 returned seems to exclude the last row of DE Mobile so that there is a sum of 33 and a count of 5 giving an avg of 6.6, but why should be that. Am I missing something? In the straight table, I do not see duplicates or anything else I should consider here.

Thank you in advance,

Francesco

1 Solution

Accepted Solutions

Re: Pivot table understanding indent rows aggregation with average - help

I think averages can get tricky... look here Average – Which average?

You might be able to do this

Sum({$< _MTD_TY = {1}, Pos={'>=1<=10'}>}Pos)/Count(DISTINCT {$< _MTD_TY = {1}, Pos={'>=1<=10'}>} DateField)

4 Replies

Re: Pivot table understanding indent rows aggregation with average - help

What expression are you using in your chart for Sum of Position? What all dimensions do you have?

fvalente
New Contributor II

Re: Pivot table understanding indent rows aggregation with average - help

Hi Sunny,

Thanks for your question...the Sum of Position has no expression. What I am trying to show is that in excel I presented all the rows that should have an average of 5.5 while sense is presenting as 5.67.

In qlik sense the 5.67 is given by a set analysis expression: Avg({$< _MTD_TY = {1}, Pos={'>=1<=10'}>}Pos)

MTD TY should pick all the 4 rows in the excel as well as the filter Pos={'>=1<=10'} which only takes POS from 1 to 10 (all the rows are in this range).

Thanks,

Francesco

Re: Pivot table understanding indent rows aggregation with average - help

I think averages can get tricky... look here Average – Which average?

You might be able to do this

Sum({$< _MTD_TY = {1}, Pos={'>=1<=10'}>}Pos)/Count(DISTINCT {$< _MTD_TY = {1}, Pos={'>=1<=10'}>} DateField)

fvalente
New Contributor II

Re: Pivot table understanding indent rows aggregation with average - help

Thank you Sunny for your reply, I eventually found, thanks to your hint, where the QSense number in the pivot was coming from....the pivot takes into account some rows which should not be there ...I will not use avg in the pivot then but perhaps work with an expression like you have proposed. Marking the correct answer thanks.

Community Browser