
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
[Qlik Sense] Count Product Above/ Below Avg Value by Dimension
Hi guys,
I have a problem with Count If Aggr Value
I have Raw data Table:
RAW_DATA:
LOAD * INLINE [
Region, Product,Revenue,TotalOrder,Rev/Order
South, A, 200, 2, 100
South, B, 500, 5, 100
South, C, 700, 1, 700
North, D, 200, 2, 100
North, E, 300, 5, 60
North, F, 500, 4, 125
North, G, 800, 4, 200
];
I have a Table on my sheet with:
- Dimension: region
- Measure Rev/Order: sum(Revenue) / sum(TotalOrder)
- Total Product: Count(distinct Product)
- No of Product Above Rev/Order Value:
count(if(Aggr(sum(Revenue) / sum(TotalOrder),Product) > $(=(sum(Revenue) / sum(TotalOrder))),Product))
- No of Product Below Rev/Order Value
count(if(Aggr(sum(Revenue) / sum(TotalOrder),Product)<= $(=(sum(Revenue) / sum(TotalOrder))),Product))
I'd like compare 2 value: Rev/Order by Product Vs. Rev/Order by Region and count(product) of them (Above or Below Rev/Order by Region)
Please let me know how to do this in Qlik Sense.
Thanks in advance.
Justin.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try these two expressions
Count(Aggr(If((Sum(Revenue)/Sum(TotalOrder)) > (Sum(TOTAL <Region> Revenue)/Sum(TOTAL <Region>TotalOrder)), Product), Product, Region))
Count(Aggr(If((Sum(Revenue)/Sum(TotalOrder)) <= (Sum(TOTAL <Region> Revenue)/Sum(TOTAL <Region>TotalOrder)), Product), Product, Region))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problem is your aggr it over only product !! make the aggr on the region and product that should solve your problem

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try these two expressions
Count(Aggr(If((Sum(Revenue)/Sum(TotalOrder)) > (Sum(TOTAL <Region> Revenue)/Sum(TOTAL <Region>TotalOrder)), Product), Product, Region))
Count(Aggr(If((Sum(Revenue)/Sum(TotalOrder)) <= (Sum(TOTAL <Region> Revenue)/Sum(TOTAL <Region>TotalOrder)), Product), Product, Region))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
It's worked.
Thank you for your help
Justin
