topic Pivot Totals Incorrect When Rows Collapsed in QlikView

Pivot Totals Incorrect When Rows Collapsed

newuser — Sun, 24 Feb 2019 23:59:38 GMT

Hello - the measure expression below in my pivot table only works when all 3 dimensions are expanded. When the dimensions are collapsed, the total is just a grand total, without the set analysis invoked. Any ideas how to fix? I tried Dimensionality() but didn't work.

The 3 dimensions used are main, Region, and Branch below. The pivot table is grouped by Region, Branch, and main (in that order). "yr" and "method2" are the columns of the pivot table (in that order). Thanks

=if(Dimensionality()<= 2 ,count(
aggr(count(( IF(
Aggr(distinct
sum({<method2={'Web'}>}totalpmt)/Sum(total <main, [yr], [Region] , [Branch]> totalpmt)
main,yr, [Region], [Branch])>=$(UsageAbove),
1))
),main, [Region], [Branch], yr)), 1

)

Re: Pivot Totals Incorrect When Rows Collapsed

ashishkalia — Mon, 25 Feb 2019 02:12:55 GMT

Create a variable and store:
vVariable = Sum(total <main, [yr], [Region] , [Branch]> totalpmt)

Set Expression:

SUM(
IF(
Aggr(
sum({<method2={'Web'}>}totalpmt)/$(vVariable)
,main,yr, [Region], [Branch]
)
>=$(UsageAbove),1)
)

Re: Pivot Totals Incorrect When Rows Collapsed

newuser — Mon, 25 Feb 2019 23:13:26 GMT

For some reason, the totals look correct (collapsed or expanded rows) when I used this simpler express below. How does one know to use AGGR in the measure vs. when not to use AGGR in a measure? Thanks

=sum({<pay_method2={'Web'}>}totalpmt)