Calculation Stock Start and STock End

[theboss-123]

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

[edwin]

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

[edwin]

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

[edwin]

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

[theboss-123]

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 

[theboss-123]

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 .

[edwin]

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

[theboss-123]

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

[edwin]

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

[edwin]

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/In.... 

[theboss-123]

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