8 Replies Latest reply: May 14, 2017 9:55 AM by Sunny Talwar RSS

    Aggr() result for each segment in the aggregation

    David Pastrana

      Hi! I'm new using Qlik Sense Desktop.

       

      I have a problem with an expression for a calculation with this data:

      Customer_IdVisit_IdProduct_IdCategory_IdInStock
      CU11P1C11
      CU11P2C10
      CU11P3C21
      CU11P4C21
      CU12P1C11
      CU12P2C10
      CU12P3C20
      CU12P4C21

       

      Results I need are, for each visit, the sum of the column InStock divided by the quantity of products, then aggregate using average:

      Customer_IdTOTALC1C2
      CU162.5% = (3/4 + 2/4) / 250% = (1/2 + 1/2) / 275% = (2/2 + 1/2) / 2

       

      So, if I use the expression Avg(Aggr(Sum(InStock)/Count(InStock), Visit_Id)), I successfuly got the result for TOTAL Customer, but not for each category. I tried adding the Category_Id in the agregation, but it gives me wrong result for the total since the average takes the results for each Visit and Category.

       

      Is there any way to achieve what I want?

       

      Any help will be very appreciate.

       

      Best regards!