Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
komminenianusha
Partner - Contributor III
Partner - Contributor III

Year on Year comparison on Table in qliksense

Hi Team,

I want to show table with current sales and previous sales . I have data from 2017 to 2021 for every year it has to show last year comparison in the table.

PFA for sample data and requirement details

6 Replies
Taoufiq_Zarra

@komminenianusha  In load Script for example :

Data:
LOAD Date( Date#(Date,'MM/DD/YYYY'),'MMM-YYYY') as MonthYear , Date#(Date,'MM/DD/YYYY') as Date, Sales INLINE [
    Date, Sales
    01/01/2020, 100
    02/01/2020, 200
    03/01/2020, 300
    04/01/2020, 400
    05/01/2020, 500
    06/01/2020, 600
    07/01/2020, 700
    08/01/2020, 800
    09/01/2020, 900
    10/01/2020, 1000
    11/01/2020, 1100
    12/01/2020, 1200
    01/01/2019, 150
    02/01/2019, 270
    03/01/2019, 380
    04/01/2019, 420
    05/01/2019, 460
    06/01/2019, 500
    07/01/2019, 540
    08/01/2019, 580
    09/01/2019, 620
    10/01/2019, 660
    11/01/2019, 700
    12/01/2019, 740
];

Tmp:

load year(max(Date)) as MaxDate resident Data;

LET vMaxDate = peek('MaxDate');

drop table Tmp;

left join (Data) load Addmonths(Date,12) as Date,Sales as SalesPY resident Data where Year(Date)=$(vMaxDate)-1;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
komminenianusha
Partner - Contributor III
Partner - Contributor III
Author

Thanks @Taoufiq_Zarra  for your reply.  This solution is only working for current and Prior years only because you are fetching data based on maxdate but I have data from 2017 to 2021 for other years this solution is not working. Can you please help me to get same kind of output for 2017 to 2021.

Taoufiq_Zarra

@komminenianusha  you can test this new solution :

Data:
LOAD Date( Date#(Date,'MM/DD/YYYY'),'MMM-YYYY') as MonthYear , Date#(Date,'MM/DD/YYYY') as Date, Sales INLINE [
    Date, Sales
    01/01/2020, 100
    02/01/2020, 200
    03/01/2020, 300
    04/01/2020, 400
    05/01/2020, 500
    06/01/2020, 600
    07/01/2020, 700
    08/01/2020, 800
    09/01/2020, 900
    10/01/2020, 1000
    11/01/2020, 1100
    12/01/2020, 1200
    01/01/2019, 150
    02/01/2019, 270
    03/01/2019, 380
    04/01/2019, 420
    05/01/2019, 460
    06/01/2019, 500
    07/01/2019, 540
    08/01/2019, 580
    09/01/2019, 620
    10/01/2019, 660
    11/01/2019, 700
    12/01/2019, 740
];


output:

load Date,lookup('Sales','Date',Addmonths(Date,-12),'Data') as SalesPY,Sales resident Data;

drop table Data;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
komminenianusha
Partner - Contributor III
Partner - Contributor III
Author

@Taoufiq_Zarra  Thanks for your new solution but this is taking  more time to load data usually it takes 1minute but now it is taking more than 25mins. Can you please help me to get other simplified solution.

Taoufiq_Zarra

@komminenianusha  ok what about this :

Data:
LOAD Date(Date#(Date,'MM/DD/YYYY'),'MMM-YYYY') as MonthYear ,Date(Addmonths(Date#(Date,'MM/DD/YYYY'),-12),'MM/DD/YYYY')  as Monthtmp, Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date, Sales INLINE [
    Date, Sales
    01/01/2020, 100
    02/01/2020, 200
    03/01/2020, 300
    04/01/2020, 400
    05/01/2020, 500
    06/01/2020, 600
    07/01/2020, 700
    08/01/2020, 800
    09/01/2020, 900
    10/01/2020, 1000
    11/01/2020, 1100
    12/01/2020, 1200
    01/01/2019, 150
    02/01/2019, 270
    03/01/2019, 380
    04/01/2019, 420
    05/01/2019, 460
    06/01/2019, 500
    07/01/2019, 540
    08/01/2019, 580
    09/01/2019, 620
    10/01/2019, 660
    11/01/2019, 700
    12/01/2019, 740
];
Map1:

mapping load Date,Sales resident Data;



output:
noconcatenate

load Date,applymap('Map1',Monthtmp,'') as SalesPY,Sales resident Data;

drop table Data;

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

@komminenianusha  also try below. assuming your Date field is in proper Date format.

Data:
LOAD Date,
     addyears(Date,-1) as Date_PY,
     Sales
FROM Tabel;

Left Join(Data)
LOAD Date as Date_PY,
     Sales as SalesPY
Resident Data;

drop field Date_PY;