Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Use the num of the date to calculate it.
so num(LastTransDate) and num(ancienne_LastTransDate)
try to use floor
IF(floor(DATE#(LastTransDate,'YYYY/MM/DD'))-floor(DATE#(Ancienne_LastTransDate,'YYYY/MM/DD'))>=365,'>=365','<365') AS YearRatio
Seems to be ok, but the script has 'AS YearRatio' but the table is 'RatioYear'. Are you using the correct field?
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;
The calc you have used seems correct
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.
would you be able to share a sample file to look at?
Hello @Kushal_Chawda ,
Please find in the attached files a sample.
Many thanks in advance for your help.
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.