topic Re: Calculation Stock Start and STock End in App Development

Calculation Stock Start and STock End

theboss-123 — Wed, 25 May 2022 11:43:14 GMT

Hello , we have Database related to ERP which include all transaction of supply chain,i explain my problem by the table bellow

Date	Start_Stock	Income	Outcome	End_stock
01/01/2022	0	100	50	50
31/01/2022	50	200	100	150

in 01/01/2022 we 0 as start of stock there are income of 100 and utcome of 50 so end of stock is 50 in qlikview i success to got income and outcome

i want to creat a variable where we put values of Start Stock and put also End stock

Start stock must have the value of the previous End Stock i hope that it is clear can u help me please t developp my dahsboard in qlikview

Re: Calculation Stock Start and STock End

edwin — Wed, 25 May 2022 15:04:53 GMT

are you saying the table shown is the output table you want?

and that you calculate Income, Outcome and End_Stock? how about Start?
can you post sample data?

here is a sample if start needs to be computed from 0:

dimension is Date, start (Column 1):

=if(isnull(above(Column(4))),0,above(Column(4)))

column 4 (End):

=column(1)+Column(2)-Column(3)

Re: Calculation Stock Start and STock End

theboss-123 — Wed, 25 May 2022 16:18:04 GMT

Hi that is right the shown table must look like the table on qlikview

I can calculate income and outcome but for star of stock we need to declare a variable so after that we could calculate end of stock and when the date increase the start of stock must equal to end of stock of previous d?

Re: Calculation Stock Start and STock End

theboss-123 — Wed, 25 May 2022 16:20:43 GMT

I will try your formula and going back to you soon

Re: Calculation Stock Start and STock End

edwin — Wed, 25 May 2022 16:43:07 GMT

just replace the 0 in the start expression:

=if(isnull(above(Column(4))),$(vStartVariable),above(Column(4)))

Re: Calculation Stock Start and STock End

theboss-123 — Wed, 25 May 2022 19:59:14 GMT

Hi Edwin thanks for help regarding the variable "vstartvariable" does I must declared it before or I run the expression directly?

Re: Calculation Stock Start and STock End

theboss-123 — Wed, 25 May 2022 22:38:43 GMT

Hi again Edwin i tried you formula but it dosen't work on my real table

Column(1)==if(isnull(above(Column(4))),$(vStartVariable),above(Column(4)))

Column(4)=Column(1)+Entréé-Sortie

Re: Calculation Stock Start and STock End

theboss-123 — Fri, 27 May 2022 11:29:06 GMT

Hello is there any solution for our case ?

To make the idea a bit clear,the start stock of each article must be 0,because before integration of data to the ERP X3 the stock was 0 for each ITEM,so regarding the transaction of income and outcome the quantity change.

The start Date is 24/01/2015 .

Re: Calculation Stock Start and STock End

edwin — Fri, 27 May 2022 12:14:05 GMT

you pls post sample data?

Re: Calculation Stock Start and STock End

theboss-123 — Fri, 27 May 2022 12:36:10 GMT

Please Find enclosed the sample Data. i work avec QLIKVIEW DESKTOP V12.20

Re: Calculation Stock Start and STock End

edwin — Fri, 27 May 2022 13:33:01 GMT

you are apparently using QlikView but your post is in Qlik Sense. i am able to pull in your data but i cant see any visualization. so pls tell me what is your expression for Entree and Sortie? ill try to help as i have time on my hands

Re: Calculation Stock Start and STock End

edwin — Fri, 27 May 2022 14:02:50 GMT

i just realized you changed the problem, adding a 2nd dimension changes the solution.

as the calculation for Entree and Sortie is irrelevant, i just threw in a random calculation:

as i am using column numbers, the order is important. also, having the 2nd dimension, chnaged the determination of the prior record. to simplify the solution i opted to use Rowno() to determine the 1st record. you can try it out on your own, add the two dimenstions and add Rowno() as a measure, you will see it starts with 1 everytime the first dimension changes. so if you test for rowno()=1 it is the first occurrence of dimension 1.

so now the start measure is

=if(rowno()=1,0,above( Column(4))) -> if this is the first record for dimension1, = 0, else get the 4th measure.

the 4th measure is still the same:

=Col1 + Col2 - Col3

im sure there could be a smarter solution for this but i hope this helps

Re: Calculation Stock Start and STock End

edwin — Fri, 27 May 2022 14:05:09 GMT

i just noticed Sortie is already negative, so your 4th measure should be Col1 + Col2 + Col3

Re: Calculation Stock Start and STock End

theboss-123 — Sat, 28 May 2022 22:55:00 GMT

First I would like to thank you for your effort to help me

To calculate entree I got combinaision of all trades section of income like receipt of an order ,inventory and more the same thing for the outcome sortie

I will try your below suggestion

Re: Calculation Stock Start and STock End

theboss-123 — Sat, 28 May 2022 23:45:50 GMT

Hello ,

Your solution solv 50% of the issue,but when i select another range of that it take the first line COl1 as 0 which make the quantity wrong please find below a comparaison between the screenshoot of stock on the ERP X3 and on QLIKVIEW,Your expression is Correct but when i select a range of date the col1 in the first line is 0 however it had a quantity before .

Re: Calculation Stock Start and STock End

edwin — Mon, 30 May 2022 01:31:02 GMT

you did say "To make the idea a bit clear,the start stock of each article must be 0"

what i think you meant was without any selection the stock starts at 0 which makes sense to me. i was wondering why you wanted it to always start from 0. but it was you who asked for it that way. let me take a look tomorrow unless someone else gives you an answer

Re: Calculation Stock Start and STock End

theboss-123 — Mon, 30 May 2022 11:38:42 GMT

Thanks Edwin for your reaction,you are close to find a solution just try to do your best please

The start stock globaly must have 0

the start stock of a selection of date range must give me the stock quanity of the before column4

Re: Calculation Stock Start and STock End

edwin — Tue, 31 May 2022 18:38:03 GMT

quickest solution i can think of is this (someone else im sure can come up with a better one)

1. create a new date field that is independent of your date field. you will allow the user to select this new field.

2. create 2 new variables: vMinDate and vMaxDate which represent the lower and upper bounds of the selected dates

3. in your table create this date dimension (i didnt explore much and maybe this can be simpler):

=if(
IPTDAT_0>=date('$(vMinDate)')
and IPTDAT_0<=date('$(vMaxDate)')
and (aggr( sum( {<Transaction={'Entrée diverse'}>}[Qantité]),IPTDAT_0, Dim3)>0 or
aggr( sum( {<Transaction={ 'Sortie diverse'}>}[Qantité]), IPTDAT_0, Dim3)<0
)
,
IPTDAT_0
)

4. your income:

Sum({<IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"},Transaction={'Entrée diverse'}>}[Qantité])

5. your sortie:

Sum({<IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"},Transaction={'Sortie diverse'}>}[Qantité])

6. start:

rangesum(above(Sum({<IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"},Transaction={'Entrée diverse'}>}[Qantité]),
1,rowno()))+
rangesum(above(Sum({<IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"},Transaction={'Sortie diverse'}>}[Qantité]),
1,rowno()))+
+aggr( nodistinct Sum( {<IPTDAT_0={"<$(vMinDate)"},Transaction={'Entrée diverse', 'Sortie diverse'}>}[Qantité]), Dim3)

7. your end :

rangesum(above(Sum({<IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"},Transaction={'Entrée diverse'}>}[Qantité]),
0,rowno()))+
rangesum(above(Sum({<IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"},Transaction={'Sortie diverse'}>}[Qantité]),
0,rowno()))+
+aggr( nodistinct Sum( {<IPTDAT_0={"<$(vMinDate)"},Transaction={'Entrée diverse', 'Sortie diverse'}>}[Qantité]), Dim3)

the solution is to use range sum and above. notice that in the above for start, the range starts one row above the current row. for end, above starts at current row. and the aggr expression just sums everything before the selected date range. i suspect the aggr will come back as null when there are no rows before the selected dates so maybe just add an if isnull and replace with 0 if null else use the aggr

Re: Calculation Stock Start and STock End

edwin — Tue, 31 May 2022 18:40:41 GMT

the reason my solution requires a new date field is that if you use the same field as the selected field, you will have problems associating the current date with prior dates unless you create some relationship which you may not have an appetite for.

heres the above function:

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/ChartFunctions/InterRecordFunctions/above.htm#:~:text=Above()%20evaluates%20an%20expression,being%20the%20row%20directly%20above.

Re: Calculation Stock Start and STock End

theboss-123 — Tue, 31 May 2022 22:49:51 GMT

I would like to thank you for your effort,i try to follow your steps, how can i creat a date fields i have already IPTDAT_0 fields