Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
swati_rastogi27
Creator
Creator

Aggregations on the fly

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.

Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

@swati_rastogi27  Try:

Dim: REGION

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

tresesco_0-1601043383434.png

 

View solution in original post

10 Replies
swati_rastogi27
Creator
Creator
Author

Anyone has suggestions plz?

tresesco
MVP
MVP

@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
Creator
Creator
Author

Just posted

Brett_Bleess
Support (Former)
Support (Former)

@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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
tresesco
MVP
MVP

@swati_rastogi27 

Take product as second dimension and use expression like:

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

tresesco_0-1601014281421.png

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
Creator
Creator
Author

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

@swati_rastogi27 

You mean like : 

tresesco_0-1601015858352.png

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

 

swati_rastogi27
Creator
Creator
Author

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

Rank_Test.png

tresesco
MVP
MVP

@swati_rastogi27  Try:

Dim: REGION

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

tresesco_0-1601043383434.png

 

View solution in original post