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

[theboss-123]

Hi Edwin i inform that i folowed your instruction and i got result but it is not equal to what it is in the ERP X3

for example  i took the item  "120500000028" :

[theboss-123]

i think Edwin that we did our best

[theboss-123]

I want to add the dim "LOT_O" as mentionned above to see if that will have an impact does i need to add LOT_0 to measrement expression for column 1,2,3 ?

and how can i sort from the late date to early based on the info =if(
IPTDAT_0>=date('$(vMinDate)')
and IPTDAT_0<=date('$(vMaxDate)')
and (aggr( sum( {<ID_Transaction={"1","3","5","13","28","31","16","19"}>}[Qantité]),IPTDAT_0, Dim3)>0 or
aggr( sum( {<ID_Transaction={"2","4","6","17","27"}>}[Qantité]), IPTDAT_0, Dim3)<0
)
,
IPTDAT_0
)

[edwin]

adding an additional dimension complicates the scenario.

ill check what i did with your test case (120500000028)

[theboss-123]

Ok in the above example the dim Lot_0 is included

I will wait for ur reply thanks Edwin

[edwin]

to put things in perspective, the solution i gave you worked for the problem you posted.  this is the picture for DIM3 = 120500000028

when you select the dates:

the numbers are correct:

30/06/2017 starts off where 06/02/2017 ended:

adding other dimensions require tweaks to the expression as the aggregation is different.  but the solution is the same.  you just need to play arround with the aggregation.  

[theboss-123]

Please try to compare with the result below and show me how can i sort informtion of table by date ,because date in the table which i have are randomly ,

this screen captured for item 120400000038  and site "Dim4" = U3100 ,

in the table above they added LOT_0 Dim because they sort quanitity by batch number (LOT)

[theboss-123]

To compare with our system ERP in which site "Dim4" u did your search ?

[edwin]

you never mentioned Dim4.  in your problem statement there wasnt even a Dim3, then you added it in.  now your problem is that what if you add LOT_0. 

to solve that, add LOT_0 as your 2nd dimension, then just change the 1st measure to:

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()))+

if(isnull(aggr( nodistinct Sum( total <Dim3, LOT_0>{<IPTDAT_0={"<$(vMinDate)"},Transaction={'Entrée diverse', 'Sortie diverse'}>}[Qantité]), Dim3, LOT_0)),0,
aggr( nodistinct Sum( total <Dim3, LOT_0>{<IPTDAT_0={"<$(vMinDate)"},Transaction={'Entrée diverse', 'Sortie diverse'}>}[Qantité]), Dim3, LOT_0))

using this as example:

then if you select dates starting from 01/07/2018 you get the following:

where 20396 is the total prior to 01/07/2018

[edwin]

these are my filters: