2 Replies Latest reply: Aug 28, 2015 5:28 AM by Marcellino Groothof Schroder

# Total dimensions in pivot table

Good morning!!

I have a problem with the total of dimensions in a pivot table. I have a report that uses alternate states for calculating a formula between two periods, but I need that the total of the dimensions are the sum of the values... and not the formula.

I tried to use the aggr function grouping by dimensions, but don´t works.

Expression of column "Alternate State Formula":

column(1)

*

Sum(TOTAL <DIM1,DIM2,DIM3> Aggr(sum({<TIP_INFO=[Group PL 1]::TIP_INFO,YEARMONTH=[Group PL 1]::YEARMONTH>} PORC),DIM1,DIM2,DIM3))

*

(

Sum(TOTAL <DIM1,DIM2,DIM3> Aggr(sum({<TIP_INFO=[Group PL 2]::TIP_INFO,YEARMONTH=[Group PL 2]::YEARMONTH>} TC),DIM1,DIM2,DIM3))

-

Sum(TOTAL <DIM1,DIM2,DIM3> Aggr(sum({<TIP_INFO=[Group PL 1]::TIP_INFO,YEARMONTH=[Group PL 1]::YEARMONTH>} TC),DIM1,DIM2,DIM3))

) / Sum(TOTAL <DIM1,DIM2,DIM3> Aggr(sum({<TIP_INFO=[Group PL 2]::TIP_INFO,YEARMONTH=[Group PL 2]::YEARMONTH>} TC),DIM1,DIM2,DIM3))

Any idea?. I've attached a sample file.

Bests regards!

• ###### Re: Total dimensions in pivot table

Hi ,

Please use for the totals a formula like

Max(aggr(NoDistinct  Expression,Dim1,Dim2))

Thanks

Kiran Kumar

• ###### Re: Total dimensions in pivot table

Hi, try this:

column

(1)
*
avg(aggr(Sum( Aggr(sum({<TIP_INFO=[Group PL 1]::TIP_INFO,YEARMONTH=[Group PL 1]::YEARMONTH>} PORC),DIM1,DIM2,DIM3))
*
(
Sum( Aggr(sum({<TIP_INFO=[Group PL 2]::TIP_INFO,YEARMONTH=[Group PL 2]::YEARMONTH>} TC),DIM1,DIM2,DIM3))
-
Sum(Aggr(sum({<TIP_INFO=[Group PL 1]::TIP_INFO,YEARMONTH=[Group PL 1]::YEARMONTH>} TC),DIM1,DIM2,DIM3))
) /
Sum( Aggr(sum({<TIP_INFO=[Group PL 2]::TIP_INFO,YEARMONTH=[Group PL 2]::YEARMONTH>} TC),DIM1,DIM2,DIM3)),DIM1,DIM2,DIM3))