Aggregations on the fly

[swati_rastogi27]

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.

[swati_rastogi27]

Anyone has suggestions plz?

[tresesco]

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

[swati_rastogi27]

Just posted

[Brett_Bleess]

@tresesco  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

[tresesco]

@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.

[swati_rastogi27]

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?

[tresesco]

@swati_rastogi27 

You mean like : 

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

[swati_rastogi27]

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

[tresesco · Accepted solution]

@swati_rastogi27  Try:

Dim: REGION

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