Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
jacek_k
Contributor III
Contributor III

Qlik Sense script add new column with addyear sales

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:

IDDateSalesNewSales
A01.01.202050-
A01.01.202110050
B01.01.202140-
C01.01.2021200-

 

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


 

Labels (2)
1 Solution

Accepted Solutions
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

7 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
fernando_tonial
Partner
Partner

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.

Screenshot_15.png

Don't worry, be Qlik.
Tonial

GaryGiles
Specialist
Specialist

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

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

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

jacek_k
Contributor III
Contributor III
Author

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:

jacek_k_0-1614953699123.png

 

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



jacek_k
Contributor III
Contributor III
Author

ID is very import in this scenario.

jacek_k
Contributor III
Contributor III
Author

Works! Great!