Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ) )
SKU | YearWeek ISO | Qty |
---|---|---|
cccc | 201351 | 5 |
cccc | 201351 | 3 |
cccc | 201402 | 2 |
dddd | 201352 | 6 |
dddd | 201401 | 2 |
dddd | 201403 | 4 |
cccc | 201403 | 8 |
cccc | 201404 | 3 |
dddd | 201404 | 2 |
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
Try Sum( [Qty] ) / Count(distinct total [YearWeek ISO] )
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
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 ?
actually I don't have control on the raw source, so I cannot fill the missing values
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
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