topic Re: Set Analysis on rolling 12 months for Inventory Turnover in QlikView

Set Analysis on rolling 12 months for Inventory Turnover

Thu, 02 Feb 2017 23:17:51 GMT

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!

Re: Set Analysis on rolling 12 months for Inventory Turnover

sunny_talwar — Fri, 03 Feb 2017 01:11:28 GMT

Does this look right?

Expression:

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

Re: Set Analysis on rolling 12 months for Inventory Turnover

Fri, 03 Feb 2017 10:41:48 GMT

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