Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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!

2 Replies
kkkumar82
Valued Contributor III

Re: Total dimensions in pivot table

Hi ,

Please use for the totals a formula like

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

Hope it may help you.

Thanks

Kiran Kumar

marcellino
New Contributor III

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

Community Browser