Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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 ?

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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