Difference between Dates

[Black_Hole]

Hello All,

I try to calculate the difference between two dates with a condition.

If the difference between the date is upper to one year then put the value '>365' else '=<365'.

But the result is no correct.

Below my script and a screenshot of the result:

[DateDiff]:
LOAD DISTINCT
Item,
IF(DATE(DATE#(LastTransDate,'YYYY/MM/DD'),'YYYY/MM/DD')-DATE(DATE#(Ancienne_LastTransDate,'YYYY/MM/DD'),'YYYY/MM/DD')>=365,'>=365','<365') AS YearRatio
RESIDENT CalculationDates;

Please could you tell me what is wrong in my script?

Thank you in advance for your help.

[bramkn]

Use the num of the date to calculate it. 

so num(LastTransDate) and num(ancienne_LastTransDate)

[Kushal_Chawda]

try to use floor

IF(floor(DATE#(LastTransDate,'YYYY/MM/DD'))-floor(DATE#(Ancienne_LastTransDate,'YYYY/MM/DD'))>=365,'>=365','<365') AS YearRatio

[RogerStone]

Seems to be ok, but the script has 'AS YearRatio' but the table is 'RatioYear'. Are you using the correct field?

[prasadmundewadi]

I think you might need to add the 2 date fields to your load script:

[DateDiff]:
LOAD DISTINCT
LastTransDate, Ancienne_LastTransDate, Item,
IF(DATE(DATE#(LastTransDate,'YYYY/MM/DD'),'YYYY/MM/DD')-DATE(DATE#(Ancienne_LastTransDate,'YYYY/MM/DD'),'YYYY/MM/DD')>=365,'>=365','<365') AS YearRatio
RESIDENT CalculationDates;

[prasadmundewadi]

The calc you have used seems correct

[Black_Hole]

Hi @prasadmundewadi, @Kushal_Chawda ,

I tried both but none give me the correct result. I think that my issue is due to the line below of my script:

 IF(LEN(Ancienne_LastTransDate)<=0,'2019/12/31', Ancienne_LastTransDate) AS LastTransDate

Maybe, the value '2019/12/31' is not considered as a date. That's why, the difference of my conditional IF is not done.

Please could you tell me if that can explain why I have in any case the result "<365" and the result is never equal to ">=365".

Thank you in advance for your help.

[Kushal_Chawda]

would you be able to share a sample file to look at?

[Black_Hole]

Hello @Kushal_Chawda ,

Please find in the attached files a sample.

Many thanks in advance for your help.

[Kushal_Chawda]

Based on sample, I can see that both Dates you are subtracting is actually the same date. This could be the reason you are not getting correct results.