[Qlik Sense] Count Product Above/ Below Avg Value by Dimension

[justinphamvn]

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.

[avinashelite]

Problem is your aggr it over only product !! make the aggr on the region and product that should solve your problem

[sunny_talwar · Accepted solution]

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

[justinphamvn]

Hi Sunny,

It's worked.

Thank you for your help

Justin