Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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:
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.
@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:
@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.
@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;
@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;