Aggregation is not working

[#1]

Hi All,

I have 4 dimensions like GROUP, SUBGROUP,PRODUCT,SUBPRODUCT.

Measures SALES,CAL_SALES,ITEMS,DIV

I created a pivot table.CAL_SALES:

For Product P1 SP1,SP2,SP3,SP4,SP5 are the SubProducts.

For Product P2 SP6,SP7,SP8,SP9,SP10 are the SubProducts.

If I minimize Products,Subproducts >>> Need to show Averages of SubProducts,Products.

If I minimize Group,SubGroups >>> Need to show the total of (Averages of SubProducts,Products).

I have used the below expression:

=IF(Dimensionality()=4,AVG(SALES),

IF(Dimensionality()=3,aggr(AVG(SALES),PRODUCT),

IF(Dimensionality()=2,SUM(aggr(AVG(SALES),PRODUCT)),

IF(Dimensionality()=1,SUM(aggr(AVG(SALES),PRODUCT)),

IF(Dimensionality()=0,SUM(aggr(AVG(SALES),PRODUCT,GROUP,SUBGROUP))

)))))

This formula is not working in my real application.

Please check the screens and app.

Thanks in advance.

[avinashelite]

I could see the formula is working ..what is the logic your logic for could you explain with an example ?

[#3]

Hi Avinash,

Formula is working in the sample data. But not working in my application.

If I minimize Products,Subproducts >>> Need to show Averages of SubProducts,Products.

If I minimize Group,SubGroups >>> Need to show the total of (Averages of SubProducts,Products).

Could you advise in re-writing the expression?

stalwar1

jagan mohan rao appala

mrkachhiaimp

gysbert wassenaar‌

swuehl

[MK_QSL]

If you mean to say that this formula is working here but not in your real application .. you should provide that sample application or respective data to work.

[avinashelite]

as per your inputs try this

=IF(Dimensionality()=5,AVG(SALES),

IF(Dimensionality()=4,aggr(AVG(SALES),PRODUCT,SUBPRODUCT),

IF(Dimensionality()=3,(aggr(AVG(SALES),PRODUCT,SUBPRODUCT)),

IF(Dimensionality()=2,SUM(aggr(AVG(SALES),PRODUCT,SUBPRODUCT)),

IF(Dimensionality()=1,SUM(aggr(AVG(SALES),PRODUCT,GROUP,SUBGROUP))

)))))

[stabben23]

Hi, I dont really get it, but try this one:

=IF(Dimensionality()=4,AVG(SALES),

IF(Dimensionality()=3,avg(aggr(sum(SALES),PRODUCT)),

IF(Dimensionality()=2,avg(aggr(sum(SALES),PRODUCT)),

IF(Dimensionality()=1,avg(aggr(sum(SALES),PRODUCT)),

IF(Dimensionality()=0,avg(aggr(sum(SALES),PRODUCT,GROUP,SUBGROUP))

)))))

[sarasunagaram]

Hi,

can you try below one,

IF(Dimensionality()=1, Aggr(Avg(SALES),GROUP),

if(Dimensionality()=2, Aggr(Avg(SALES),SUBGROUP),

if(Dimensionality()=3, Aggr(Avg(SALES),PRODUCT),

if(Dimensionality()=4, Aggr(Avg(SALES),SUBPRODUCT))

)

)

)

if you are using SUM(Aggr(Avg(SALES),SUBGROUP)) also it will give same thing.

[rabbani_sk]

Hi Gopi,

As per my investigation. This formula is working fine. But, from your words,

"Formula is working in the sample data. But not working in my application". What you mean by this statement ?

Please mention where are you exactly facing the issue with the above solution  ?

Any ways, I am re-writing the expression with few changes,

=IF(Dimensionality()=4,AVG(SALES),

IF(Dimensionality()=3,SUM(aggr(AVG(SALES),PRODUCT)),

IF(Dimensionality()=2,SUM(aggr(AVG(SALES),PRODUCT)),

IF(Dimensionality()=1,SUM(aggr(AVG(SALES),PRODUCT)),

IF(Dimensionality()=0,SUM(aggr(AVG(SALES),PRODUCT,GROUP))

)))))

[MK_QSL · Accepted solution]

If you are using conditional show in Pivot Table and only showing one dimension at a time then dimensionality () would be always 1. In that case you don't need such a complex expression.