7 Replies Latest reply: Mar 8, 2018 9:22 AM by Andrea Gigliotti RSS

    Count number of ocurrences of metric above certain value in a table

    Anibal Martinez-Sistac

      I have this table of Master Items in Qlik:

      Operation IDPriceRevenueProfit
      10.233.20120%
      20.96-2.15200%
      30.501.18105%
      40.10-2.16116%
      ............

       

      • I have created a new table with 1 Master item: "Product Name" and a calculated field "Operations", whose formula is: num(Count(DISTINCT [Operation id]),'#,##0',',' )
      • The attribute "Profit" is as well a calculated metric, whose formula is: num((Sum(Win) / Sum(Pay))*100,'0.00') &'%' where Win and Pay are in fact master items.


      . What I would like to do is to add 4 desired metrics, which are:

      1. Number of operations with Profit > 115%
      2. Percentage of operations with Profit > 115%
      3. Number of operations with Profit between 100% and 105%
      4. Percentage of operations with Profit between 100% and 105%

       

      The look of my desired table would be something like this:

      Product NameOperationsNumber of Operations with Profit > 115%Percentage of Operations with Profit > 105% Number of Operations with Profit between 100% and 105% Percentage of Operations with Profit between 100% and 105%
      Vynils3220............
      Memorabilia1150............
      CDs430............
      DVDs810............
      ..................

       

      I thank before-hand for any help on the expression or Qlik set analysis expression required to achieve my desired table. Anibal

       

      Mensaje editado por: Anibal Martinez-Sistac