Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to divide the current stock by the average sales for the last 3 months (incl. current month) to give the number of months in stock.
I have built a report that shows on year at a time for all 12 months. Selecting the year displays the months of that year.
The problem is that this year selection means that any months in the previous year are filtered out of the calculation so the first 2 months are incorrectly calculated.
How do I resolve this?
Filtered on 2018
Nov 17 Dec 17 Jan 18 Fed 18
Sales 500 400 100 50
Stock 1,300 1,500
Avg sales (should be) 400 285
Stock turn (should be) 3.25 5.26
As the previous years are excluded...
Avg sales (are... ) 250 202
Stock turn (are...) 5.20 7.41
Sorry for the confusion, I got the numbers wrong (below the corrected numbers)...
Nov 17 Dec 17 Jan 18 Fed 18
Sales 500 400 100 50
Stock 1,300 1,500
Avg sales (should be) 333 183 (Avg for Jan = (Nov, Dec & Jan) / 3) etc.
Stock turn (should be) 3.90 8.18
As the previous years are excluded...
Avg sales (are... ) 100 75
Stock turn (are...) 13 20
I use a set analysis to ensure that I have data for all of the previous months but then other filters do not affect the result.
I would like a result that can then react for any subsequent filtering....