3 Replies Latest reply: Jun 1, 2018 2:32 AM by Justin Pham RSS

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

    Justin Pham

      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.