topic Re: Stock Running Balances in QlikView

Stock Running Balances

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Stock Running Balances

maksim_senin — Wed, 18 May 2016 07:38:57 GMT

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

Re: Stock Running Balances

shamitshah — Wed, 18 May 2016 08:11:02 GMT

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

Re: Stock Running Balances

Wed, 18 May 2016 08:26:11 GMT

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.

Re: Stock Running Balances

shamitshah — Wed, 18 May 2016 08:33:53 GMT

Hi Harshal,

I have attached the excel file which has the data.

Thanks

Shamit

Re: Stock Running Balances

Wed, 18 May 2016 08:47:27 GMT

Hi,

PFA,

please use below expression for Past 30 days stock ,

rangesum(above(Movement,-30))

Re: Stock Running Balances

shamitshah — Wed, 18 May 2016 09:48:46 GMT

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

Re: Stock Running Balances

Wed, 18 May 2016 09:57:45 GMT

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

Re: Stock Running Balances

Wed, 18 May 2016 10:11:29 GMT

Try like below for avg stock in last 30 days,

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

Re: Stock Running Balances

shamitshah — Wed, 18 May 2016 11:45:36 GMT

Please see attached file with sample desired result.

Thanks

Shamit

Re: Stock Running Balances

shamitshah — Wed, 18 May 2016 22:07:38 GMT

File attached below.

Thanks

Shamit

Re: Stock Running Balances

Tue, 24 May 2016 09:53:59 GMT

Hi ,

PFA,

Start of day & End of Day stock calculated.Please verify it.

But, could you please check for past 30 day logic N 3rd row of Excel.