topic Total dimensions in pivot table in QlikView

Total dimensions in pivot table

Fri, 28 Aug 2015 08:55:57 GMT

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

kkkumar82 — Fri, 28 Aug 2015 09:19:54 GMT

Hi ,

Please use for the totals a formula like

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

Hope it may help you.

Thanks

Kiran Kumar

Re: Total dimensions in pivot table

Marcellino_Groothof — Fri, 28 Aug 2015 09:28:42 GMT

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