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]

add the following at the end of your load script or before the exit script statement if you haev it:

NoConcatenate
Dates:
load distinct date(IPTDAT_0,'DD/MM/YYYY') as IPTDAT_0_1
resident Journal_Stock;

this will create a new field called IPTDAT_0_1, of course you can use any field name you want.

then you allow the user to select this field and not IPTDAT_0

[theboss-123]

Hi Edwin

i Added the new field of date,in the step 2 i created two variable vMinDate which i affected to it =min(IPTDAT_0_1)

and vMaxDate which is max(IPTDAT_0_1) but nothing change.

[edwin]

you cant just use min(date variable) - it retiurns a number and not a date.  you need to use:

date(min(date field)), if the default format is diff, you should add the desired format:
date(min(date field), 'format here')

[theboss-123]

Hi Edwin

I tried both to affect vMinDate to date(min(IPTDATE_0)  and date(min(IPTDATE_0_1) and nothing change the same thing for vMaxDate

Can u work on my sample and send me back the file ?

[theboss-123]

Hi EDWIN i hope that you are doing very well,my issues is not solved

[edwin]

i tried the following in Qlik Sense:

create variables:
vMinDate
=date(min(IPTDAT_0),'DD/MM/YYYY')

vMaxDate
=date(max(IPTDAT_0),'DD/MM/YYYY')

let the user select the field IPTDAT_0, the variables will automatically store the first and last date selection. 

create table with the following dims:

Dim3

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

creat the following measures:

1:

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>{<IPTDAT_0={"<$(vMinDate)"},Transaction={'Entrée diverse', 'Sortie diverse'}>}[Qantité]), Dim3)),0,
aggr( nodistinct Sum( total <Dim3>{<IPTDAT_0={"<$(vMinDate)"},Transaction={'Entrée diverse', 'Sortie diverse'}>}[Qantité]), Dim3))

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

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

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

qlik sense and qlikview should work the same way.

for this test i selected the following:
IPTDAT_0={">=01/01/2018<=10/10/2019"}

to validate, following is the straight totals for all dates:

[edwin]

this i susing your data from the QVW

[theboss-123]

Really I greatly appreciated your time availibility efforts, i will try it in my envirrenement

thanks

[theboss-123]

Hi Again EDWIN,

i tied your config and i adapted it to my envirrenement it return resulst but i have some comment , it not return details for Dim3 (Item) it gives just cumul of stock,and second comment when i select IPTDAT_0 it return numbers not date please find bellow the screenshoot

[theboss-123]

here is the update