6 Replies Latest reply: Jan 24, 2014 3:44 AM by anant dubey RSS

    Retail sales: how to compute average sales per SKU ?



      I need to display average sales per SKU over a period selected by the user (last 3/6/9/12 weeks etc.)

      What I need is a true average over the period, even if the data is missing in the row source (so I cannot use the formula: Sum( [Sold Qty] ) / Count( distinct Week ) )


      SKUYearWeek ISOQty


      As you can see in the example above, data are missing when there is simply no sales, but I need to consider those "zero" sales data.

      If the current week is 201404, and user is computing the average of last 6 weeks (201404 to 201351), the result should be:

      ccccc: (5+3+2+8+3) / 6 = 3.5pcs per week

      dddd: (6+2+4+2) / 6 = 2.3pcs per week



      To help the user, I display the list of YearWeekISO (no missing data) and I let them choose the week they need and I computer the average based on this formula: Sum( [Sold Qty] ) / count(distinct  [YearWeek ISO])


      This is working fine until the user filter too much. For example, if a user wants to focus only on item cccc, then when he selects the last 6 YearWeek ISO, qlikview will automatically deselect the ones with no data, consequently the count(distinct  [YearWeek ISO]) will be wrong....


      So is there a way to select all YearWeek ISO, even the one with no data ?

      Or maybe someone has another way to achieve what I want


      ==> I could use an input box and the user input the number of weeks for the average, but this is not user friendly when they use large period (they need to manually calculate the # of weeks, can do mistakes etc.)


      Thank you