Subtract values from previous values

[ahmd]

Hi,

How to subtract "PL" values from "Previous ST" and each time new ST to be caluclated.

For example on 22.01.2021 I have 1 "Previous ST" in table 1, but the SL in table 2 takes place on different dates and they are subtracting mostly in each dates. So, I have to subtract Previous Day ST - Today SL each time.

The table 1 consist on ST with 3 dimensions.

Table 1
Dim1	Dim2	Date	Previous ST
A	A1	22.01.2021	18
A	A2	22.01.2021	3
B	B1	22.01.2021	11
B	B2	22.01.2021	17
C	C1	22.01.2021	14
C	C2	22.01.2021	13

Table 2 consist on SL which are taking place on different dates.

Table 2
Dim1	Dim2	Date	SL
A	A1	23.01.2021	1
A	A1	24.01.2022	2
A	A1	25.01.2022	2
A	A1	26.01.2023	4
A	A1	27.01.2023	5
A	A1	28.01.2024	1
A	A2	23.01.2021	1
A	A2	24.01.2022	2
B	B1	23.01.2021	2
B	B1	24.01.2022	1
B	B2	25.01.2022	1

Output required: Formula: New ST = Previous ST - SL

Table 2
Dim1	Dim2	Date	Previous ST	SL	New ST
A	A1	22.01.2021	19
A	A1	23.01.2021		1	18
A	A1	24.01.2022		2	16
A	A1	25.01.2022		2	14
A	A1	26.01.2023		4	10
A	A1	27.01.2023		5	5
A	A1	28.01.2024		1	4
A	A2	22.01.2021	3	1
A	A2	23.01.2021		1	2
A	A2	24.01.2022		2	0

[Dalton_Ruer]

Check out this post it was a similar question and I think the solution will work for you as well. 

[Dalton_Ruer · Accepted solution]

https://community.qlik.com/t5/App-Development/Historical-Stock-Level-from-Current-Stock-and-Transact...

[ahmd]

Hi,

It works for specific ST Date with lookup. For example date is 22.01.2021 in table 1.

If the dates are changed with ST then it doesn't handle. 

Dim1, Dim2, Date, Previous ST
A, A1, 22.01.2021, 18
A, A1, 27.01.2021, 5

[MayilVahanan]

T1:
LOAD * INLINE [
Dim1, Dim2, Date, Previous ST
A, A1, 22.01.2021, 19
A, A2, 22.01.2021, 3
B, B1, 22.01.2021, 11
B, B2, 22.01.2021, 17
C, C1, 22.01.2021, 14
C, C2, 22.01.2021, 13
];

Concatenate
LOAD * INLINE [
Dim1, Dim2, Date, SL
A, A1, 23.01.2021, 1
A, A1, 24.01.2022, 2
A, A1, 25.01.2022, 2
A, A1, 26.01.2023, 4
A, A1, 27.01.2023, 5
A, A1, 28.01.2024, 1
A, A2, 23.01.2021, 1
A, A2, 24.01.2022, 2
B, B1, 23.01.2021, 2
B, B1, 24.01.2022, 1
B, B2, 25.01.2022, 1
];

FinalTemp:
Load *, If(Len(Trim(SL))>0, Peek(NewST)-SL, ([Previous ST])) as NewST Resident T1 order by Dim1, Dim2, Date;

Final:

NoConcatenate
LOAD Dim1, Dim2, Date, [Previous ST], SL, if(Len(Trim([Previous ST]))=0,NewST)as NewST Resident FinalTemp;
DROP Table T1, FinalTemp;

O/P: