Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shamitshah
Partner - Creator
Partner - Creator

Stock Running Balances

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

11 Replies
maksim_senin
Partner - Creator III
Partner - Creator III

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
Partner - Creator
Partner - Creator
Author

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

Not applicable

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
Partner - Creator
Partner - Creator
Author

Hi Harshal,

I have attached the excel file which has the data.

Thanks

Shamit

Not applicable

Hi,

PFA,

please use below expression for Past 30 days stock ,

rangesum(above(Movement,-30))

shamitshah
Partner - Creator
Partner - Creator
Author

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

Not applicable

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

Not applicable

Try like below for avg stock in last 30 days,

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

shamitshah
Partner - Creator
Partner - Creator
Author

Please see attached file with sample desired result.

Thanks

Shamit