Qlik Sense script add new column with addyear sales

[jacek_k]

Sample Data:

LOAD * INLINE [
ID, Date, Sales
A, 01.01.2021, 100
A, 01.01.2020, 50
B, 01.01.2021, 40
C, 01.01.2021, 200
];

Result:

ID	Date	Sales	NewSales
A	01.01.2020	50	-
A	01.01.2021	100	50
B	01.01.2021	40	-
C	01.01.2021	200	-

Find Sales where 01.01.2021 same day year ago by specific ID.

[PrashantSangle]

Test:
LOAD ID,
Date(Date#(Date,'DD.MM.YYYY')) as Date,
Sales
INLINE [
ID, Date, Sales
A, 01.01.2021, 100
A, 01.01.2020, 50
B, 01.01.2021, 40
C, 01.01.2021, 200
];

Join

LOAD ID as ID,
Addyears(Date,1) as Date,
Sales as new_Sales
Resident Test
Where Exists(ID,ID) and Exists(Date,Addyears(Date,1))
;

Regards,

Prashant Sangle

[fernando_tonial]

Hi, you can try this:

TMP:
LOAD * INLINE [
ID, Date, Sales
A, 01/01/2021, 100
A, 01/01/2020, 50
B, 01/01/2021, 40
C, 01/01/2020, 100
C, 01/01/2021, 200
];

Data:
LOAD
	ID, 
    Date, 
    Sales,
    If(ID=Previous(ID) and 
       AddYears(Date,-1)=Previous(Date),
         Sales-Previous(Sales)) AS NewSales
Resident TMP
Order By
	ID, Date;

Drop Table TMP;

This is the result.

Don't worry, be Qlik.
Tonial

[GaryGiles]

Wasn't exactly sure of your date format, so you may need to adjust the script, but this should provide the desired results:

SampleData:
LOAD ID, Date#(Date,'MM.DD.YYYY') as Date,Sales INLINE [
ID, Date, Sales
A, 01.01.2021, 100
A, 01.01.2020, 50
B, 01.01.2021, 40
C, 01.01.2021, 200
];

Left Join (SampleData)
Load ID,
           AddYears(Date,1) as Date,
           Sales as NewSales
Resident SampleData;

[PrashantSangle · Accepted solution]

Hi @GaryGiles , 

you can more optimise your code by using where exist in resident table. It will create only those data which available in base table.

modifying your code like

Test:
LOAD ID,
Date(Date#(Date,'DD.MM.YYYY')) as Date,
Sales
INLINE [
ID, Date, Sales
A, 01.01.2021, 100
A, 01.01.2020, 50
B, 01.01.2021, 40
C, 01.01.2021, 200
];

Join

LOAD ID as ID,
Addyears(Date,1) as Date,
Sales as new_Sales
Resident Test
Where Exists(ID,ID) and Exists(Date,Addyears(Date,1))
;

Regards,

Prashant Sangle

[jacek_k]

I changed data:

Load * Inline [
ID, Date, Sales
F, 02.01.2018, 25
A, 01.01.2021, 100
A, 02.01.2020, 50
B, 01.01.2021, 40
C, 01.01.2021, 200
C, 01.01.2020, 200
A, 02.01.2019, 13
A, 01.01.2020, 50
F, 02.01.2019, 50
];

Result:

Perheps I need to use a function other than the previous.

[jacek_k]

ID is very import in this scenario.

[jacek_k]

Works! Great!