Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
micheledenardi
Specialist II
Specialist II

Change Total Formula

Dear all,

starting from the sample data attached, in Qlik Sense i want to create a table with a simple average of quantity per product and in the total the summary of all averages (not the grand average).

So i would like to specify one formula for the details and another one for totals. is it possible?

Below the result i have to achieve.

2019-10-14 09_53_10-SampleData.xlsx - Salvataggio completato.png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
Labels (2)
1 Solution

Accepted Solutions
tomovangel
Partner - Specialist
Partner - Specialist

Hello Micheledenardi ,

 

I am attaching the Qlik Sense application with needed result. 

the way is to get Product in a straight table(as dimension) and use the following measure.

sum(aggr(sum(Quantity)/count(Quantity),Product))

 

View solution in original post

3 Replies
tomovangel
Partner - Specialist
Partner - Specialist

Hello Micheledenardi ,

 

I am attaching the Qlik Sense application with needed result. 

the way is to get Product in a straight table(as dimension) and use the following measure.

sum(aggr(sum(Quantity)/count(Quantity),Product))

 

micheledenardi
Specialist II
Specialist II
Author

Thanks Tomovangel,

your solution works on straight table.

What if i want to use the same login on a pivot table where the aggr() level depends from the table dimensions expansion?

If the pivot table is completelly compressed i have to apply an aggr() by the first dimension, if the table has the first two dimension expanded i have to apply another aggr() by the firsts two dimension and so on... i have to setup something similar to a dynamic aggr() funcion.

I don't want to set my pivot table always fully expanded.

 

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
micheledenardi
Specialist II
Specialist II
Author

Solved by myself using SecondaryDimensionality().

So

if(SecondaryDimensionality()=1,    //Pivot table is completelly collapsed

sum(aggr(sum(Quantity)/count(Quantity),Product)),

if(SecondaryDimensionality()=2,  //Pivot table has 1st dimension expanded

sum(aggr(sum(Quantity)/count(Quantity),ProductCategory,Product))

))

 

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.