Qlik Sense Pivot Table Aggregation

[Gabe_Miller]

Hello, I have been asked to update an existing Pivot table to show an aggregation percent at each level.  I have attached an example of what the table currently looks like, and what the desired view is as well.  

Current calculation for % of Sales:

Sum(Aggr(Sales{<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Sales),[VP Name],Branch))
/
Sum(Total{<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Sales)

I am looking to get the aggregation of the branches to sum up at a VP level. So VP S has 15.9% of total sales and branch 30 has 3.9% of Total Sales.  But I would like to have this Pivot Table show Branch 30 as 24.4% of sales for  VP S (103,692.63/424,618.66) instead.  

Is it possible to have both aggregation layers show without having to select a VP Name to adjust the 'Total' sales amount?

[brunobertels · Accepted solution]

Hi 

not sure to understand your need but it seems who want the % of VP Name sales on TOTAL Sales and % of Branch sales on total sales PER VP Name in the same mesure. 

Am I Wright ? if so you can use dimentionnality () to do that with an if statement 

here a simple exemple : 

if(Dimensionality()=1,

sum(Volume)
/
sum(total Volume), // it will divide sum sales per TOTAL sum Sales then 

sum(Volume)
/
sum(total <DIM2> Volume) // it wil divide sales per Sub Total per DIM2 ( branch for you ) 
)

hopt it helps 

[Gabe_Miller]

Thank you, @brunobertels ! This helps solve my initial problem.  I am able to assign dimensionality based on the order of dimensions in the Pivot table.  

My new issue is that if the order of the dimensions is changed then the calculation errors.  Is there a way to assign dimensions based on order, similar to Column(1), Column(2) for measures?

Ex: Dimension Order:  VP_Name, Branch, Product      
       Reordered Dimension:    VP_Name, Product, Branch

Please let me know if this question makes sense?

[brunobertels]

Hi 

it makes sense. 

You will be able to rearrange your mesure depending of the order of the dimension using getObjectDimension() function 

this function give the name of the dimension according to his index 

So it will be someting like this 

if(GetObjectDimension(Dimensionality()-1) ='Branch', YourMesureForBranchHere , YourMesureForProductHere)

this :

GetObjectDimension(Dimensionality()-1) will give you the Name of the second dimention in your PivotTable