topic Re: Aggregation is not working in QlikView

Aggregation is not working

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Aggregation is not working

avinashelite — Thu, 02 Feb 2017 06:59:11 GMT

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

Re: Aggregation is not working

Thu, 02 Feb 2017 07:14:09 GMT

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

Re: Aggregation is not working

MK_QSL — Thu, 02 Feb 2017 07:26:18 GMT

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.

Re: Aggregation is not working

avinashelite — Thu, 02 Feb 2017 07:26:24 GMT

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))

)))))

Re: Aggregation is not working

stabben23 — Thu, 02 Feb 2017 08:35:51 GMT

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))

)))))

Re: Aggregation is not working

sarasunagaram — Thu, 02 Feb 2017 12:48:47 GMT

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.

Re: Aggregation is not working

rabbani_sk — Thu, 02 Feb 2017 12:52:23 GMT

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))

)))))

Re: Aggregation is not working

MK_QSL — Fri, 03 Feb 2017 06:13:42 GMT

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.