
Re: Pivot table understanding indent rows aggregation with average  help
Sunny Talwar Feb 6, 2018 12:26 PM (in response to Francesco Valente)What expression are you using in your chart for Sum of Position? What all dimensions do you have?

Re: Pivot table understanding indent rows aggregation with average  help
Francesco Valente Feb 7, 2018 3:50 AM (in response to Sunny Talwar )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
Sunny Talwar Feb 7, 2018 7:21 AM (in response to Francesco Valente)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)

Re: Pivot table understanding indent rows aggregation with average  help
Francesco Valente Feb 12, 2018 9:50 AM (in response to Sunny Talwar )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.


