How to make columns subtract from the prior one

[robinwiman]

I'm trying to create a table for our logistics department where they want to see how we predict stock levels to decrease and eventually go to zero (0) - if we don't refill of course.

So I have a table where I have the following columns:

Column 1: Name of the store

Column 2: Current stock level this week

Column 3 and forward (one for each week): Current stock level subtracted with forecasted sales.

So column 3 would be: Sum ([Column2] - QtyBu) pretty much.

And column 4 would be Sum ([Column3] - QtyBu) etc. until we enter into the negative stock levels.

Can I create a feature that weekly subtracts what is expected to be sold against what was last week's stock?

What I am really asking, I guess, is if it is possible to do the weekly subtraction in a single expression.

I have all the neccessary dimensions, variables and tools to do it. I just don't know how.

[swuehl]

How does your data model look like,i.e. how is your stock level and forecast stored?

Maybe create a common time dimension for your stock and forecast.

Then use a pivot table with store name vertical and time dimension starting  this week and going an appropriate period into the future, pivoted to the top / horizontal.

Your expression could look like

=Rangesum( Sum(TOTAL<Store> {<ThisWeek = {1} >} Stock), -Before( Sum(QtyBu) ,0, ColumnNo() ) )

Basically use chart inter record functions like Before() / Above() in combination with a Rangesum() function to aggregate all weeks forecast up to the dimensional week.

[shraddha_g]

try sum(column(2)-QtyBu)

Else

Column(2) - Sum(QtyBu)

[robinwiman]

I know this method, and it would've worked if I only had two columns. But you can't use this method if you have more than 1 column.

[robinwiman]

Stock level and forecast can be shown on a daily basis and per store.

We have lots of time dimensions like this week, prior week, next week, this week last year etc.

I'm going to try the function you suggest and report back thanks

[robinwiman]

I should mention that I am using a dimension called "Year Week", which is basically what it says. This week for the current year.

I would like to show the table columns as:

Current stock level | This week (40) | 2017 w41 | 2017 w42 etc...