topic Re: Difference between Dates in QlikView

Difference between Dates

Black_Hole — Sat, 16 Nov 2024 01:35:10 GMT

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.

Re: Difference between Dates

bramkn — Mon, 06 Jan 2020 16:00:25 GMT

Use the num of the date to calculate it.

so num(LastTransDate) and num(ancienne_LastTransDate)

Re: Difference between Dates

Kushal_Chawda — Mon, 06 Jan 2020 16:00:27 GMT

try to use floor

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

Re: Difference between Dates

RogerStone — Mon, 06 Jan 2020 16:03:54 GMT

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

Re: Difference between Dates

prasadmundewadi — Mon, 06 Jan 2020 16:10:20 GMT

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;

Re: Difference between Dates

prasadmundewadi — Mon, 06 Jan 2020 16:11:33 GMT

The calc you have used seems correct

Re: Difference between Dates

Black_Hole — Tue, 07 Jan 2020 12:30:20 GMT

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.

Re: Difference between Dates

Kushal_Chawda — Tue, 07 Jan 2020 12:41:20 GMT

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

Re: Difference between Dates

Black_Hole — Tue, 07 Jan 2020 13:13:36 GMT

Hello @Kushal_Chawda ,

Please find in the attached files a sample.

Many thanks in advance for your help.

Re: Difference between Dates

Kushal_Chawda — Tue, 07 Jan 2020 13:37:00 GMT

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.

Re: Difference between Dates

Brett_Bleess — Tue, 21 Jan 2020 22:07:07 GMT

Did Kush's last post help you get things sorted? If so, be sure to circle back to your thread and use the Accept as Solution button on that post to give him credit for the help and to let other Community Members know that worked. If you did something else, consider posting that, and then mark it, so everyone knows what the issue was and what you did to fix it. If you still need further help, leave an update post.

Regards,
Brett