topic SUM OF ROWS PIVOT TABLE in QlikView

SUM OF ROWS PIVOT TABLE

Fri, 29 Jan 2016 14:17:41 GMT

I have a pivot table and I am trying to get the sum of rows , I have 2 dimensions. I wanna show partial sum for both dimensions but show the sum of rows instead of expression total.

Below is my expression

If((sum({<Year={$(=MAx(Year))},NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=>}INDEMNITY_PAID_YTD-INDEMNITY_RECOVERY_YTD)

sum({<Year={$(=MAx(Year)-1)},NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=>}INDEMNITY_PAID_YTD-INDEMNITY_RECOVERY_YTD))='0',0,

((max({<Year={$(=Max(Year))},Month = {"<=$(=Max(Month))"} ,NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=>}MthNbr)/12)

*Sum({<Year={$(=Max(Year))},NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=,Month = {"<=$(=Max(Month))"}>} ind_budget)))

Re: SUM OF ROWS PIVOT TABLE

swuehl — Fri, 29 Jan 2016 14:22:11 GMT

In general, use advanced aggregation for sum-of-rows in a pivot table:

Sum(

Aggr(

If((sum({<Year={$(=MAx(Year))},NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=>}INDEMNITY_PAID_YTD-INDEMNITY_RECOVERY_YTD)

sum({<Year={$(=MAx(Year)-1)},NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=>}INDEMNITY_PAID_YTD-INDEMNITY_RECOVERY_YTD))='0',0,

((max({<Year={$(=Max(Year))},Month = {"<=$(=Max(Month))"} ,NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=>}MthNbr)/12)

*Sum({<Year={$(=Max(Year))},NO_BOP_FLAG={'0'},ORGANIZATION_NAME= ,[Line of Business]=,Month = {"<=$(=Max(Month))"}>} ind_budget)))

, YourTableDimensionField1, YourTableDimensionField2

))