Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
pas_epm_support
New Contributor II

Total in Pivot Table

I have a pivot table in which i want to display the Totals with a different calculations for different columns. I am using the Totals option in the  pivot table to display the "Totals".

The calculation of the Totals should not be a regular sum of net_val for each fund instead it should be a based on a different formula for different column.  Currently , "Total" shows the sum of net_val for underlining funds. But i expect the "Total" have a different calculation. It should be the sum (net_val*Val ) of each fund for column ABC and sum(net_val/Val ) for column DEF.

Attached is the qvf.  

2 Replies
MVP
MVP

Re: Total in Pivot Table

With the help of the Dimensionality() function you can determine whether there is a Totals that needs to be calculated or some other row. 

 

So you could have an expression like this:

If( Dimensionality() = 1  , Sum(net_val*Val) , Sum(net_val) )

All the totals have dimensionality 1 whereas the other rows will have dimensionality 2.

In your example I couldn't find any Val field so I don't know what that is referring to... I not sure what you mean by column ABC and column DEF either...

pas_epm_support
New Contributor II

Re: Total in Pivot Table

Hello Petter,

 

Thanks for replying. I have attached the qvf again where you will be able to see ABC and DEF columns.

I am not sure where can i put this  Dimensionality() function since there is no way i can change the functionality of Totals (provided by Qlik Sense) or should i put it in the Dimension where we are calculating the Totals? 

 

ABC and DEF are different columns . I want the Totals for be calculated differently for different columns in the pivot table.

At the moment, the Totals is the aggregation of the underline rows. (funds in my example)

Dimensionality() works only for rows in a pivot table ?

Also, i would like to mention that i am using Qlik Sense Apr 2019 version.