1 Reply Latest reply: Apr 10, 2018 11:22 AM by Sunny Talwar RSS

    Set analysis aggr Only ?

    Quentin MARACHE

      Hi team !

       

      I have 2 requirements for the business that i cant' solve myself (I'm on Qlik Sense Nov. 2017) !

       

      Here my sample of data:

       

      Transaction_ID, Category, Item_Code, Qty_sold

      1, Shoes, Brown_Richelieu, 2

      1, Shoes, Tennis, 1

      1, Shoes, Black_Richelieu, 3

      2, Jeans, T54_654, 2

      2, Shoes, Tennis, 1

      3, Shoes, Black_Derbie, 3

      4, Jeans, T65_876, 2

       

      I want to sum qty sold of transactions which contains only the category "shoes":

      >> here, only for transactions ID which contains category "Shoes", (1 and 3), the result must be 9


      I want to sum qty sold of transactions which contains only the category "shoes" and "Jeans":

      >> here, only for transactions ID which contains category "Shoes" and "Jeans", (2), the result must be 3


      I have already try:

      • Sum{$<[Category] = {'Shoes'}>} [Qty_sold])
      • Aggr(Sum{$<[Category] = {'Shoes'}>} [Qty_sold]), [Transaction_ID])

       

      But it didn't work !


      Can you help me ?

      Thanks in advance !


      Quentin




        • Re: Set analysis aggr Only ?
          Sunny Talwar

          Try this

           

          1) Sum{$<Transaction_ID = {"=Category = 'Shoes' "}>} [Qty_sold])

          1) Sum({$<Transaction_ID = {"=Category = 'Shoes' "}>} [Qty_sold])

          2) Sum{$<Transaction_ID = {"=Count(DISTINCT {<Category = {'Shoes', 'Jeans'}>} Category) = 2"}>} [Qty_sold])

          2) Sum({$<Transaction_ID = {"=Count(DISTINCT {<Category = {'Shoes', 'Jeans'}>} Category) = 2"}>} [Qty_sold])


          Capture.PNG