cancel
Showing results 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!

1 Solution

Accepted Solutions
MVP

Does this look right?

Expression:

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

2 Replies
MVP

Does this look right?

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

Community Browser