topic Re: Pivot Table Total in QlikView

Pivot Table Total

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

Hi Community,

How to show missing total for below image. This is pivot table. I am getting total for only one value and not for all. I wanted to show totals for all the values.

Dim1: =aggr(only({<%Month = {'$(=$(=vPrevMonth))'}, %Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'}>}CRR),PARTY_ID)

Dim2: =aggr(only({<%Month = {'$(=$(=vCurrMonth))'}, %Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CRR),PARTY_ID)

Expression
if(
Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') =

Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
if(
Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') <

Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
Count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
if(
Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') >

Re: Pivot Table Total

Kushal_Chawda — Mon, 11 Apr 2016 08:43:50 GMT

can you post the sample application?

Re: Pivot Table Total

Mon, 11 Apr 2016 08:46:53 GMT

Hi Priya,

Provide a sample .qvw file .

Regards,

Anjali

Re: Pivot Table Total

jonathandienst — Mon, 11 Apr 2016 08:47:35 GMT

To calculate the total in the pivot table, you will need to use an Aggr() wrapped around the expression. This Aggr() needs the chart dimensions as fields, but you are using calculated dimensions, so you have to use the underlying fields. you could try:

Sum(Aggr(if(
  Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') =
  Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
  count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
  if(
  Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') <
  Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
  Count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
  if(
  Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') >
  Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
  Count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
  ) 
) 
), CRR, PARTY_ID))

But if I can make a suggestion - do the replace operations in the load script. This will make your expressions much easier to write, understand and debug, and leads to a more sustainable model.

Re: Pivot Table Total

priyarane — Mon, 11 Apr 2016 09:46:29 GMT

Thanks Jonathan,

It worked like below

Sum(Aggr(if(
Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') =
Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
if(
Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') <
Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
Count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
if(
Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') >
Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''),
Count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) ,
)
)
), PARTY_ID))