Calculate projected inventory

Paul Gemin

Hello Board Community,

 

I would like to calculate projected stocks with the following formula : Stocks = Previous month stocks + current month Supply - current month Sales.

 

I have 3 cubes : Sales, Supply and Stocks. All cubes have 3 entities : Month, Part number and Market. Sales and Supply have values for 2019 (Actual before May, and Forecast after). Stocks has data for the four first months of 2019.

 

I have tried my hand at the rules, creating a 4D cube with a new entity (Data type : Sales, Supply and Stocks), and a rule [Stocks]=[.Stocks]+[Supply]-[Sales], applied in a layout were the columns are the months and the rows are the Data Types (see below)

Unfortunately, nothing changed for the empty months. I have run a few tests with the dot rule, and I can't seem to make it work with the time entity.

 

Am I doing something wrong?

Is there an easier way to calculate projected stocks?

 

Thank you for your answers!

 

Paul

Paul Gemin

Quick update: I saw what Brendan Broughton did using a loop in a procedure. It works for me (I am at month level), but it takes 10 seconds on average to calculate the 10 next values.

 

If anyone has a way of calculating it on the fly without using procedures, I would be interested to hear about it.

 

Thank you!

Paul

Unknown

Hi Paul Gemin I wouldn't rule out doing this with a Rule or Nexel (let's see what other suggestions you receive), but you could do this with a non-looping procedure by using cumulative values.

Your process flow in the procedure is:

Where a refers to the month holding the opening stock for the first period of the calc (held in the Stock cube)

 

b is Sales, but using the Yearly Cumulative function (I set mine to cycle 999 so it would continue to calculate after the end of the year)

 

c is Supply, again the Yearly Cumulative, with the same cycle as Sales

 

Make sure your Month selection for the procedure starts after the month holding the opening stock amount because you don't want it to be overwritten.

Paul Gemin

Hello Phil Marshall, that's a very good idea, thank you!

 

I will implement it and see how much it reduces the calculation time. I will also continue looking for a solution using a rule (I don't know what Nexel are, though), as it should allow almost instant calculation without the need to use a procedure.

 

Have a nice day, and thanks again!

Paul

Paul Gemin

Quick feedback:

 

The calculation was really long because the cubes were dense. With sparse cubes, the calculation was 1-2 seconds long only.

 

I didn't know the Nexel function, but it is the best way so far to do the calculation I need without using a procedure. Here is the formula I used:

 

I hope this will help others with similar issues!