Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

justinphamvn
Contributor II

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

Count 1 .png

Expect Result.png

Please let me know how to do this in Qlik Sense.

Thanks in advance.

Justin.

1 Solution

Accepted Solutions
MVP
MVP

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

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

3 Replies

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

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

MVP
MVP

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

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
Contributor II

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

Hi Sunny,

It's worked.

Thank you for your help

Justin