- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ,
Please use for the totals a formula like
Max(aggr(NoDistinct Expression,Dim1,Dim2))
Hope it may help you.
Thanks
Kiran Kumar
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))