Select previous year date if available.

[soha1902]

Hi All,

I have Data in Below format-

Date                  Previous Year Date              Amount

2020-12-31             Null                                      100

2020-09-30             2019-12-31                        200

2020-06-30             Null                                       300

2019-12-31             Null                                       400

Now I want to show the Previous Year amount if user select the current date. For example 

if user selected 2020-09-30 then Amount will be 400(because for 2020-09-30 previous year date is 2019-12-31) and if user select any other date then amount should be 0 (Because previous year  date is Null).

Please help.

Thanks.

[soha1902]

Plz help.

[sasikanth]

HI, 

Try below expression

sum(if(not  is null(Previous Year Date), Amount, 0))

[PriyankaShivhare]

Hi,

Map_PrevY_Amount:
Mapping Load
Date as Previous_Year_Date,
Amount;

Load * inline[

Date , Amount

2020-12-31 , 100

2020-09-30 , 200

2020-06-30 , 300

2019-12-31 , 400

];

Table:
Load Date,
Previous_Year_Date,
ApplyMap('Map_PrevY_Amount',Previous_Year_Date,0) as PrevAmount,
Amount;
Load * inline[

Date , Previous_Year_Date , Amount

2020-12-31 , Null , 100

2020-09-30 , 2019-12-31 , 200

2020-06-30 , Null , 300

2019-12-31 , Null , 400

];

Hope this Helps!

Thanks,

Priyanka