topic Re: Qlik Sense Pivot Table Aggregation in App Development

Qlik Sense Pivot Table Aggregation

Gabe_Miller — Tue, 21 Dec 2021 21:56:36 GMT

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?

Re: Qlik Sense Pivot Table Aggregation

brunobertels — Wed, 19 May 2021 10:45:35 GMT

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

Re: Qlik Sense Pivot Table Aggregation

Gabe_Miller — Wed, 19 May 2021 14:27:51 GMT

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?

Re: Qlik Sense Pivot Table Aggregation

brunobertels — Wed, 19 May 2021 15:33:54 GMT

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