Stock Running Balances

[shamitshah]

Hi,

Please see attached file in which I have Sales Orders, Purchases , Adjustments for each day.

The current stock balance is 5.

I want to insert three columns:

1) show the stock balance at the start of each day,

2) show the stock balance at the end of each day, and

3) show the average stock for the past 30 days on each day

Any suggestions as to how I can do that?

Thanks

Shamit

[maksim_senin]

Hi Shamit,

There is no way just to insert a column. First you should define an algorithm which is to be used to calc each parameter. After you have to load initial data, usually this is done with some ordering & grouping and aggregations with use of Previous() and Peek() functions in order to sum previously loaded values.

I'm not sure I understand semantics of your data so avoiding to provide any formulas.

Best regards,

Maxim

[shamitshah]

Hi Maxim,

Just to clarify, are you implying that this can only be done in the load script and not by additional expressions in the table?

Thanks

Shamit

[#4]

Hi,

For calculating start day , end day , past 30 day stock you need to have timestamp in your data based on which you will restrict data inside table using set analysis.

Could you share DateTime field of your data.

[shamitshah]

Hi Harshal,

I have attached the excel file which has the data.

Thanks

Shamit

[#6]

Hi,

PFA,

please use below expression for Past 30 days stock ,

rangesum(above(Movement,-30))

[shamitshah]

Hi Harshal,

The result is not what I am expecting.

Your calculations do not show the stock balance on each day, therefore not sure how you can calculate the average stock.

Thanks

Shamit

[#8]

Hi,

based on your existing dates calculation was made with previous table rows .

If possible could you create sample calculation at excel level manually which will help us to track your desired output.

Thanks

[#9]

Try like below for avg stock in last 30 days,

rangesum(above(Movement,-30))/30 & check your output

[shamitshah]

Please see attached file with sample desired result.

Thanks

Shamit