Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I need help from Qlik experts 🙂
Context
In order to clean up our archives, I try to display the age (in months) of all the documents in order to delete the oldest ones and free up space.
Each document has a creation date. Therefore all you have to do is calculate a duration (today's date - the creation date) to obtain the age of the document.
However, I have two issues:
Issue 1: Creation date has a particular format: Wed Oct 27 14:26:52 2021
I'm not sure Qlik recognizes this format as a date and therefore if it's possible to use them as an expression.
How to change this format to a more classic format: MM/DD/YYYY (don't need Hours, Minutes and Seconds)
Issue 2: Which expression to use to calculate the duration between two date (today's date - the creation date) ?
Thanks in advance for your help 🙂
If Qlik doesn't recognize that as a date, you can always break it down using strings, concatenate those, and rebuild it into a date.
Right(Creation_Date,4) is year
Mid(Creation_Date,5,3) is month name
Trim(Mid(Creation_Date,9,2)) would be the day (Trim is necessary to remove the excess space in case of a single-digit day)
You could then use e.g. date#(DayPart & '/' & MonthPart & '/' & YearPart,'d/mmm/yyyy') - note that this may not be the precise format but I'm sure you can get it to work with a little fiddling around.
It worked. thx a lot 🙂