
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date difference with respect to today's date
Hi all,
I have to calculate the date difference with reference to today() in terms of years,months and days
please let me how to achieve this in qlik sense.
Regards,
Arun
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
this formula should work for short time differences.
=Year(Today() - datecol + 1) - 1900 & ' Years, '
& Num(Month(Today() - datecol + 1) - 1) & ' Months, '
& Day(Today() - datecol + 1) & ' Days'
Two things:
it gives incorrect result for datecol = Today - simple if can solve that
it gives you one extra day for each Leap Day - this seems to be much harder to solve...
BR,
Matus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is the expected output?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
expected output for '28-Dec-14' - '20-Dec-16' is 2 years, 0 months,8days


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Interval(now() - Datecolumn,'YY')
Interval(now() - Datecolumn,'MM')
Interval(now() - Datecolumn,'DD')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Interval can give out Year and Month? I don't think Interval can do this unless this is something new?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you can try like this ,but this is not recommended.
( year(Today())- year(datecol))&'Years :'&( month(Today())- month(datecol))&'Months : '&( Day(Today())- Day(datecol))&'Days '


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It gives 1 year, -11 months, -30 days between 2016/01/01 and 2015/12/31.
It is technically correct but not usable...
BR,
Matus


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe something along this?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
this formula should work for short time differences.
=Year(Today() - datecol + 1) - 1900 & ' Years, '
& Num(Month(Today() - datecol + 1) - 1) & ' Months, '
& Day(Today() - datecol + 1) & ' Days'
Two things:
it gives incorrect result for datecol = Today - simple if can solve that
it gives you one extra day for each Leap Day - this seems to be much harder to solve...
BR,
Matus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you matus kelemen
it worked for me..
