Hi,
I want to work out how many months coverage of each SKU I have.
At present I take the current stock figure called "StockAvailability" and divide it by (number of units sold divided by 12). I then select the 12 months I want to make up the Sum(Units) total by highlighting those months.
sum (StockAvailability) / (sum(Units)/12)
I have added columns to the attachment to show how it calculates
[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8688.StockAvail.ppt:550:0]
The problem is that by doing it this way I need to select 12 months everytime otherwise the formula will be wrong. If I only highlight 6 months then the formula will calculate the total units for the 6 months and then divide by 12 which will skew the results.
What I want to do is be able to select a different time period, for example over 18 months and the formula count the number of months I select.
An example script would be:
sum (StockAvailability) / ( sum(Units)/Count of Periods selected)
I just don't know how to do the last bit in code.
Avg doesn't work as if a SKU was only sold in 3 of the 6 months I select the Avg will only work it out on 3 months.
Any ideas?
David
