Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Krish2459_58
Creator
Creator

date format

Hi,

I need to show the output highlidhted below in red().

If the difference review date-today() >0 I need to show the difference in the format as highlighted in red().

And one addon like if review date-today() < 365 I need to show like 2 months 11 days instead of 0 years 2 months 11 days.

 

ref review date =today() review date-today() difference Required output
000AL2 01/07/2024 21/04/2024 71 <365 2 months 11 days
000AL2 01/07/2026 21/04/2024 801 >365 2 Years 2 months 11 days
000AL2 01/07/2029 21/04/2024 1897 >365 5 Years 2 months 12 days
000AL2 01/07/2032 21/04/2024 2993 >365 8 Years 2 months 13 days
000AL2 01/07/2035 21/04/2024 4088 >365 11 Years 2 months 13 days
Labels (1)
4 Replies
Krish2459_58
Creator
Creator
Author

Any Suggestions please 🙂

Chanty4u
MVP
MVP

Try this 

LOAD
ref,
[review date],
Date(today()) as today,
Date([review date]) as review_date,
Date([review date]) - Date(today()) as review_date_diff
FROM [source];

LOAD
ref,
[review date],
today,
review_date,
review_date_diff,
If(review_date_diff > 0,
If(review_date_diff < 365,
Floor(Months(review_date_diff)) & ' months ' & Mod(review_date_diff, 30) & ' days',
Floor(review_date_diff / 365) & ' years ' & Floor(Months(Mod(review_date_diff, 365))) & ' months ' & Mod(review_date_diff, 30) & ' days'),
'') as Required_output
RESIDENT YourData;

Krish2459_58
Creator
Creator
Author

@Chanty4u ,

Thanks for the reply.

 

It seems the logic adds 30 days. In the below 70 days from today is 2 months 10 days but the logic give 3 months 10 days.

Krish2459_58_0-1713772546069.png

Thanks..

Krish2459_58
Creator
Creator
Author

any additional suggestions please.