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

# Retail sales: how to compute average sales per SKU ?

Hello,

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
cccc2013515
cccc2013513
cccc2014022
dddd2013526
dddd2014012
dddd2014034
cccc2014038
cccc2014043
dddd2014042

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

• ###### Re: Retail sales: how to compute average sales per SKU ?

Try Sum( [Qty] ) / Count(distinct total [YearWeek ISO] )

• ###### Re: Retail sales: how to compute average sales per SKU ?

Hi Gysbert,

adding the Total helps to get accurate average.

However If the user filter too much, they can't select the "missing" YearWeek ISO in the list (they are displayed in grey) => is there a way to allow the selection of these weeks ?

• ###### Re: Retail sales: how to compute average sales per SKU ?

They are greyed/deselected by qlikview Inmemory RDBMS MODEL.

if there are weeksiso of sku  if one SKU  is select and then select your Yearweekiso field and select all six none will be de selected but if you select Multiple SKU then yes it will see those dates which are common to both SKU and those dates  which are not common to both/multiples SKU those week will be deslected.

anant

• ###### Re: Retail sales: how to compute average sales per SKU ?

Hi

This can be fixed by modifying the data model to populate every week with a dummy record with value 0 for each SKU that does not have a figure for that week.

In the front end, perhaps:

Sum( [Sold Qty] ) / (Max(Week) - Min(Week) + 1)

HTH

Jonathan

• ###### Re: Retail sales: how to compute average sales per SKU ?

actually I don't have control on the raw source, so I cannot fill the missing values

• ###### Re: Retail sales: how to compute average sales per SKU ?

hi ,

you can use a formula   =Aggr(NODISTINCT sum(qty)/\$(vInput),SKU )  in straight table with SKU as dimension

use a input box and attach variable vInput to it and let users enter weekd 2, 3,

but again choice is yours.

anant