topic Re: Aggregations on the fly in QlikView

Aggregations on the fly

swati_rastogi27 — Fri, 15 Nov 2024 23:58:53 GMT

Hi Experts,

How can we do on-the-fly aggregations using AGGR?

I have a sample data set which has Product wise Sales and Revenue data starting Jan-2020 for 3 regions -> North America , Europe and Asia. I have these use cases to solve for :

1. List the Top3 Products in terms of Sales YTD aggregated along with Aggregated Revenue for the same products-> This is solved ; Used below expression :

=if(aggr(Rank(sum(SALES)),PRODUCT_ID)<=3,sum(SALES))

2. A table which shows Sum of Sales for Top 3 Products YTD per Region and Revenue from the same products. Used this expression :

sum( {<PRODUCT_ID = {"=Rank(sum(SALES),REGION) <=3"}>} SALES)

Problem is , it splits the Total Sales obtained in point 1 across the 3 Regions .

I want absolute number per region.

3. A table which shows Sum of Sales for Top 3 Products per month and Revenue from the same products for that month

Facing the same problem as above

I have attached the QlikView file along with the Data that i'm using.

Would appreciate your help.

Re: Aggregations on the fly

swati_rastogi27 — Thu, 17 Sep 2020 07:09:40 GMT

Anyone has suggestions plz?

Re: Aggregations on the fly

tresB — Thu, 17 Sep 2020 07:35:04 GMT

@swati_rastogi27

A sample app with explanation of expected output is a better way (if not best) of seeking help with this type of scenario based issue resolution. It's a kind of

a picture is worth a thousand words

for Qlik (or for that matter : BI).

Re: Aggregations on the fly

swati_rastogi27 — Thu, 24 Sep 2020 11:12:31 GMT

Just posted

Re: Aggregations on the fly

Brett_Bleess — Thu, 24 Sep 2020 19:37:49 GMT

@tresB Just wanted to flag things in case the notifications are still not letting you guys know of updates, poster did add a QVW and data file to the post, so might be enough to offer up some suggestions, but I am not sure, as this one is over my abilities. Appreciate the look if you have time. I did just see they also just updated not long ago, so apologies if you get hit twice, but I wanted to be sure you saw things, apologies if you get two notices.

Regards,
Brett

Re: Aggregations on the fly

tresB — Fri, 25 Sep 2020 06:13:35 GMT

@swati_rastogi27

Take product as second dimension and use expression like:

=if(aggr(Rank(sum(SALES)),REGION, PRODUCT_ID)<=3,sum(SALES))

If this works, for the rest you have to adjust the measures and/or dimensions only

@Brett_Bleess , Thank you for tagging. Yes, I am still NOT getting the notifications.

Re: Aggregations on the fly

swati_rastogi27 — Fri, 25 Sep 2020 06:33:20 GMT

Unfortunately that's not an option

I cannot include another dimension .

We need 2 Summary tables - 1 by region and 1 by monthYear which would show absolute top 3 numbers

Is there some other workaround?

Re: Aggregations on the fly

tresB — Fri, 25 Sep 2020 06:47:22 GMT

@swati_rastogi27

You mean like :

To get, this - turn the chart into straight table and hide the product dimension in presentation tab.

Re: Aggregations on the fly

swati_rastogi27 — Fri, 25 Sep 2020 10:07:18 GMT

That's partly correct , but it has to be one row per region with a Total of that Region . Something like below:

Re: Aggregations on the fly

tresB — Fri, 25 Sep 2020 14:16:45 GMT

@swati_rastogi27 Try:

Dim: REGION

Exp: Sum(aggr(If(Rank(sum(SALES) )<=3,Sum(SALES)),REGION, PRODUCT_ID))

Re: Aggregations on the fly

swati_rastogi27 — Fri, 25 Sep 2020 15:45:43 GMT

Awesome ! that solved it.

Thanks so much