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 applied some update to the straight table i  added Dim1 "Number of Piece" and i disabled column3 "Sortie Divers" and i combined "Sorties DIvers" with Entree Divers in column 2, so column 3 = Column 1+ 2 and we are close to the solution of this complicated situation but it still some work

the screen of the system is  try to take a look on it please

[theboss-123]

In ur filter try with following criteria 

Dim3=120400000038

Dim4=U3100

IPTDAT_0=Between 01/01/2018 and 05/01/2018

[theboss-123]

Hi Edwin can you combine Entree DIvers and Sortie divers in the first measurement,because when i did that in the second measurement that work,now we need the correct setting for the first Measurement.

[theboss-123]

Hi EDWAN is there any update

[edwin]

i changed my expressions to use the transaction ID, i filtered using the 2 dimensions and i am getting correct numbers based on the data in you QVW.

make sure you sort the 3 dimensions as Dim3, LOT_0, 3rd dim.

i will send you the data result on Monday

[theboss-123]

Ok many thanks Edwin 

[theboss-123]

hi edwin is there any update

[edwin]

using your data:

>=05/03/2018<=05/12/2018

the result is XLS1.

without the date filter, the result is XLS2.

takefor example LOT0 :

to summarize

IPDAT0: 
=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
)

first measure which is prior total:

rangesum(above(Sum({<
IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"},
ID_Transaction={'1','3','5','13','28','31','16','19', '2','4','6','17','27'}
>}[Qantité]),
1,rowno()))
+

if(isnull(aggr( nodistinct Sum( total <Dim3, LOT_0>{<
IPTDAT_0={"<$(vMinDate)"},
ID_Transaction={'1','3','5','13','28','31','16','19', '2','4','6','17','27'}
>}[Qantité]), Dim3, LOT_0)),0,
aggr( nodistinct Sum( total <Dim3, LOT_0>{<
IPTDAT_0={"<$(vMinDate)"},
ID_Transaction={'1','3','5','13','28','31','16','19', '2','4','6','17','27'}
>}[Qantité]), Dim3, LOT_0))

2nd measure:

=sum({<ID_Transaction={'1','3','5','13','28','31','16','19'}, IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"}>}[Qantité])

3rd measure:

=sum({<ID_Transaction={'2','4','6','17','27'}, IPTDAT_0={">=$(vMinDate)<=$(vMaxDate)"}>}[Qantité])

4th measure:

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

your variables:

[dhakdhak96]

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

[theboss-123]

Hi Edwin thanks for your great effort, i tried your suggestion and that work tell me how can i sort from early to late date by column "IPDAT0: 
=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
)"

to make it closer to the reality like picture below i want to add Dim1