Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis on rolling 12 months for Inventory Turnover

Hi all,

With reference to the below chart I am trying to calculate the Inventory Turnover (where there is a '?') as it was on each Yearweek. This is calculated using the following formula:

Inventory Turnover = [Total Sales] / [Projected Inventory]

The Total Sales for each yearweek can be taken as the sales for the previous 52 weeks.

The Projected Inventory is the upper red box for each equivalent yearweek.

So for example, the Inventory Turnover value for 201717 should show 8564098/[Sum of Sales for the last 52 weeks]

Since set analysis is calculated only once per chart I am finding it a problem to find the [Sum of Sales for the last 52 weeks] for each and every yearweek.

Any suggestions how this can be achieved?

I also attached the document as it might help.


Thanks!

Untitled.png

1 Solution

Accepted Solutions
sunny_talwar

Does this look right?

Capture.PNG

Expression:

([MRP Receipts] + IF(IsNull([Carried-Forward]), 0, [Carried-Forward]) - [MRP Demand])/RangeSum((Before([Actual Sales] + [Scheduled Sales], 0, 52)))

View solution in original post

2 Replies
sunny_talwar

Does this look right?

Capture.PNG

Expression:

([MRP Receipts] + IF(IsNull([Carried-Forward]), 0, [Carried-Forward]) - [MRP Demand])/RangeSum((Before([Actual Sales] + [Scheduled Sales], 0, 52)))

Not applicable
Author

Awesome Sunny. Although the formula that I want is different from what you provided you still gave me the answer.

RangeSum((Before([Total Sales], 0, 52)))/[Projected Inventory]


I used the 'before' function before I didn't know that it can take more than one parameter!!

Always learning