Pivot Table Total

[priyarane]

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','') >

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

[Kushal_Chawda]

can you post the sample application?

[#3]

Hi Priya,

Provide a sample .qvw file .

Regards,

Anjali

[jonathandienst · Accepted solution]

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.

[priyarane]

Thanks Jonathan,

It worked like below

1. Sum(Aggr(if( 
2.   Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') = 
3.   Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''), 
4.   count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) , 
5.   if( 
6.   Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') < 
7.   Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''), 
8.   Count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) , 
9.   if( 
10.   Replace(replace(only({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR','') > 
11.   Replace(replace(only({<%Month = {'$(=$(=vPrevMonth))'},%Year = {'$(=$(=vPrevYear))'},CURRENCY = {'$(=$(vCurr))'} >}CRR),'_','.'),'CRR',''), 
12.   Count({<%Month = {'$(=$(=vCurrMonth))'},%Year = {'$(=$(=vCurrYear))'},CURRENCY = {'$(=$(vCurr))'}>}CONTRACT) , 
13.   )  
14. )  
15. ),  PARTY_ID))