Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Difference between Dates

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;

datediff.PNG

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

 

Thank you in advance for your help.

Labels (2)
10 Replies
Highlighted
Partner
Partner

Use the num of the date to calculate it. 

so num(LastTransDate) and num(ancienne_LastTransDate)

Highlighted
MVP
MVP

try to use floor

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

Highlighted
Contributor
Contributor

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

Highlighted
Contributor III
Contributor III

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;

 

Highlighted
Contributor III
Contributor III

The calc you have used seems correct

Highlighted
Creator II
Creator II

Hi @prasadmundewadi@Kush ,

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.

Highlighted
MVP
MVP

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

Highlighted
Creator II
Creator II

Hello @Kush ,

Please find in the attached files a sample.

 

Many thanks in advance for your help.

Highlighted
MVP
MVP

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.