Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Try:
Dim: REGION
Exp: Sum(aggr(If(Rank(sum(SALES) )<=3,Sum(SALES)),REGION, PRODUCT_ID))
Anyone has suggestions plz?
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).
Just posted
@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
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.
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?
You mean like :
To get, this - turn the chart into straight table and hide the product dimension in presentation tab.
That's partly correct , but it has to be one row per region with a Total of that Region . Something like below:
@swati_rastogi27 Try:
Dim: REGION
Exp: Sum(aggr(If(Rank(sum(SALES) )<=3,Sum(SALES)),REGION, PRODUCT_ID))