topic Re: Subtract values from previous values in App Development

Subtract values from previous values

ahmd — Fri, 30 Apr 2021 12:06:19 GMT

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

Re: Subtract values from previous values

Dalton_Ruer — Fri, 30 Apr 2021 13:18:38 GMT

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

Re: Subtract values from previous values

Dalton_Ruer — Fri, 30 Apr 2021 13:18:50 GMT

https://community.qlik.com/t5/App-Development/Historical-Stock-Level-from-Current-Stock-and-Transaction/m-p/1803902#M65292

Re: Subtract values from previous values

ahmd — Sun, 02 May 2021 18:06:55 GMT

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

Re: Subtract values from previous values

MayilVahanan — Mon, 03 May 2021 04:35:42 GMT

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: