Issue using aggr function in a pivot table

[AN2024]

I am using the below formula in a pivot table (I use two dimensions MATERIAL and GERENCIA):

sum({
<FECHA= {">=$(=Date(MonthStart(Max(FECHA)), 'DD/MM/YYYY'))<=$(=Date(Max(FECHA), 'DD/MM/YYYY'))"}>
} SALES) /
sum({
<FECHA= {">=$(=Date(MonthStart(Max(FECHA)), 'DD/MM/YYYY'))<=$(=Date(Max(FECHA), 'DD/MM/YYYY'))"}>
} Aggr([Días],FECHA,MATERIAL,GERENCIA)) *
Max(Aggr(If(FECHA= Max(total FECHA), TotalDías),FECHA,MATERIAL,GERENCIA))

This formula works correctly if a remove of the pivot table one of the two dimensions and if I remove of the formula the dimensions I am not using, but the problem is when I use both dimensions the calculation goes wrong, because if I put firstly GERENCIA and the MATERIAL as dimensions the results of the MATERIAL are correct but for GERENCIA is wrong, specifically I have two values in MATERIAL and for any reason I don´t understand the total value of GERENCIA is divided for two (two distinct values of MATERIAL field) and if I use first MATERIAL and then GERENCIA  the values of GERENCIA are calculated correctly but the total value for MATERIAL are divided for the distinct values I have in the GERENCIA field, in this case MATERIAL is divided in 8, but if the first dimension used in the pivot table is not divided for the distinct values of the second dimension the value of the first dimension would be calculated correctly.

So, I appreciate if any of you have any idea to resolve this problem using aggr function with more then one dimension in a pivot table.

Thanks.

[rubenmarin1]

Hi, maybe you need to use dimesnionality and apply a different expression for the first dimension that only uses it's dimension, also maybe you need to use getobjectdimension() to retrieve dinamically the dimension, in case the user moves the dimensions.

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecor...

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunct...

It could be something like:

If(Dimensionality()=1
  ,[Expression with Aggr(... ,[$(=GetObjectDimension(0))])]
  ,[Expression with both dimensions in aggr]
)

To get the desired result maybe it needs other changes, like noting that the set analysis in the first sum may be also needed in the max function.