Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Different Total calculation in Pivot Table

Hi all!!

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

KPI_data3.jpg

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.

1 Solution

Accepted Solutions
Not applicable

Re: Different Total calculation in Pivot Table

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:

KPI1.jpg

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

KPI2.jpg

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

5 Replies
MVP
MVP

Re: Different Total calculation in Pivot Table

maybe with dimensionality() and secondarydimensionality()

How to use - Dimensionality()

martin_dideriks
New Contributor III

Re: Different Total calculation in Pivot Table

The simple version.

If(SecondaryDimensionality()=0,

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

     AVG(HAUL) //else avg

)

//Martin

Employee
Employee

Re: Different Total calculation in Pivot Table

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

Not applicable

Re: Different Total calculation in Pivot Table

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:

KPI1.jpg

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

KPI2.jpg

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

Not applicable

Re: Different Total calculation in Pivot Table

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!

Community Browser