# 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
Did you mean:
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.

Labels (1)
• ### aggr

1 Solution

Accepted Solutions
MVP

@swati_rastogi27  Try:

Dim: REGION

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

10 Replies
Creator
Author

Anyone has suggestions plz?

MVP

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

Creator
Author

Just posted

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

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.

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?

MVP

You mean like :

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

Creator
Author

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

MVP

@swati_rastogi27  Try:

Dim: REGION

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

Community Browser