topic Re: Different Total calculation in Pivot Table in QlikView

Different Total calculation in Pivot Table

Tue, 09 Jun 2015 15:33:06 GMT

Hi all!!

I have a pivot table and i need different total calculation depending the dimension.

This is the expression:

=AVG(TOTAL <MONTH_SHORT_NAME, HAUL> Aggr(SUM({<MIS_KPI_ID = {'AVO_PROMEDIO'}>} VALOR_KPI),MONTH_SHORT_NAME, HAUL))

The total of HAUL (horizontal row) is an average, but the total of MONTH SHORT NAME (vertical column) is a SUM expression. How can i do this expression to make differents total calculations?

Best Regards.

Re: Different Total calculation in Pivot Table

maxgro — Tue, 09 Jun 2015 15:45:08 GMT

maybe with dimensionality() and secondarydimensionality()

How to use - Dimensionality()

Re: Different Total calculation in Pivot Table

martin_dideriks — Wed, 10 Jun 2015 06:53:03 GMT

The simple version.

If(SecondaryDimensionality()=0,

SUM(HAUL), //If total, then sum

AVG(HAUL) //else avg

)

//Martin

Re: Different Total calculation in Pivot Table

hic — Wed, 10 Jun 2015 07:36:58 GMT

Wouldn't the following work?

SUM({<MIS_KPI_ID = {'AVO_PROMEDIO'}>} VALOR_KPI) / COUNT( distinct MONTH_SHORT_NAME )

Within a column, there is only one month, which means that you get the Sum. But in a row, there are several months, so you get the average.

HIC

Re: Different Total calculation in Pivot Table

Wed, 17 Jun 2015 13:34:44 GMT

Thanks martin.dideriksen‌ the solution works fine, but i have a problem with the aggregations (for more detail see attachment).

When i have all the dimensions expanded the total is correct:

But if i collapse the dimensions, the total is wrong:

I'm not able to solve the aggr expression for totals. This is my expression:

If(secondaryDimensionality()=0,

Avg(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI> Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI)),

Sum(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI> Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI))

)

How can I modify this expression?

Thanks for help me!!

Re: Different Total calculation in Pivot Table

Wed, 17 Jun 2015 15:40:09 GMT

The solution to fix the expression has given me hic‌ :

If(secondaryDimensionality()=0,

SUM(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI> Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI))/COUNT( distinct MONTH_SHORT_NAME ),

Sum(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI> Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI))

)

Thanks all for help me!