converting YTD data into monthly numbers but the start of a new year Jan subrtracts the december data which i dont want

[vvvvvvizard]

Ignore column Control_Amt

202201 should show 6 for ActZarytd and and not 6-38

Every new year needs to start with its normal value as listed 

Every other value should be eg 2021/02/01 should show 4-1=3

2021/01/01 should just show 1 

please help 

load * inline [
_Period, Control_Amt,ActZARYTD 

load * inline [
_Period, Control_Amt,ActZARYTD
2021/01/01,1,1
2021/02/01,3,4
2021/03/01,4,8
2021/04/01,2,10
2021/05/01,3,13
2021/06/01,6,19
2021/07/01,2,21
2021/08/01,7,28
2021/09/01,2,30
2021/10/01,3,33
2021/11/01,4,37
2021/12/01,1,38
2022/01/01,6,6
2022/02/01,4,10
2022/03/01,7,17
2022/04/01,3,20
2022/05/01,4,24
2022/06/01,9,33
2022/07/01,5,38
2022/08/01,7,45
2022/09/01,8,53
2022/10/01,6,59
2022/11/01,9,68
2022/12/01,7,75
];
Qlik Sense Enterprise on Windows  #before #peek #previous

[vvvvvvizard]

i actually need it it in format 202201 only not the 01 at end that i managed to do

[vinieme12 · Accepted solution]

As below

temp:
load Date#(_Period,'YYYY/MM/DD') as _Period, ActZARYTD inline [
_Period, Control_Amt,ActZARYTD
2021/01/01,1,1
2021/02/01,3,4
2021/03/01,4,8
2021/04/01,2,10
2021/05/01,3,13
2021/06/01,6,19
2021/07/01,2,21
2021/08/01,7,28
2021/09/01,2,30
2021/10/01,3,33
2021/11/01,4,37
2021/12/01,1,38
2022/01/01,6,6
2022/02/01,4,10
2022/03/01,7,17
2022/04/01,3,20
2022/05/01,4,24
2022/06/01,9,33
2022/07/01,5,38
2022/08/01,7,45
2022/09/01,8,53
2022/10/01,6,59
2022/11/01,9,68
2022/12/01,7,75
];

//Make sure data is sorted correctly for this to work

Main:
Load 
_Period
,ActZARYTD
,if(Year(_Period)=Year(Peek(_Period)),ActZARYTD-Peek(ActZARYTD),ActZARYTD) as Control_Amt
Resident temp
Order by _Period;

drop table temp;

exit Script;

[vvvvvvizard]

Table:
load * inline [
_Period, Control_Amt,ActZARYTD
2021/01/01,1,1
2021/02/01,3,4
2021/03/01,4,8
2021/04/01,2,10
2021/05/01,3,13
2021/06/01,6,19
2021/07/01,2,21
2021/08/01,7,28
2021/09/01,2,30
2021/10/01,3,33
2021/11/01,4,37
2021/12/01,1,38
2022/01/01,6,6
2022/02/01,4,10
2022/03/01,7,17
2022/04/01,3,20
2022/05/01,4,24
2022/06/01,9,33
2022/07/01,5,38
2022/08/01,7,45
2022/09/01,8,53
2022/10/01,6,59
2022/11/01,9,68
2022/12/01,7,75
];

//////////////////////////

//Make sure data is sorted correctly for this to work
A:
LOAD *,
Date(Date#(_Period,'YYYY/MM/DD'),'YYYYMM') AS Period
Resident Table;

B:
NoConcatenate
LOAD *
Resident A
Order by Period asc;

DROP Table A;

Main:
Load
Period
,ActZARYTD
,if(Year(Period)=Year(Peek(Period)),ActZARYTD-Peek(ActZARYTD),ActZARYTD) as MonthAmount
Resident B
Order by Period asc;

drop table B;