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

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

       

       

       

      Error-Subtotal.jpg

       

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

       

      Bests regards!