How to convert four digit Julian date (yddd) to Gregorian (mm/dd/yyyy) parsable by a line graph?

[pbran]

Hey there,

So I'm fed date data that is contained in a field called "Date". It is in the format of four-digit Julian (yddd) (Yes, that's one digit for the year). Examples: 9001 would be January 1st, 2019. 2283 would be October 10th, 2012

The end goal is to have a field that Qlik can read and show a line graph on. I know at some point I have to calculate the Julian days to a month, but I can't even get past the year part. I've tried several ways, basically revolving around the following:

Right([Date], 3) as JulianDay,
Left([Date], 1) as JulianYearSingleDigit,
201 & JulianYearSingleDigit & JulianDay as TempField,
// Do Math to convert Julian day here
Date#([TempField], 'YYYYMMDD') as FormattedDate

I've tried implementing the post from https://community.qlik.com/t5/QlikView-Documents/Non-Gregorian-calendars/ta-p/1481422, but because it's four digit (and for the sake or understanding Qlik's script language) I'm doing this in the script loader.

Does anyone have any insight on this, or how I would go about this conversion? Thank you so much!

[Kashyap_R · Accepted solution]

Hi 

Try this:

LOAD
date(date#('201' & left([Date], 1) & '0101', 'YYYYMMDD') -1 + num( mid([Date], 2,4))) as FormattedDate
Resident Test3;

Thanks

[jonathandienst · Accepted solution]

This should do it:

Date(MakeDate(Left(Date, 1) + 2010) + Right(Date, 3) - 1, 'MM/DD/YYYY') as FormattedDate,

[Brett_Bleess]

Pierson, the only thing I can add is the following Design Blog link that might help:

https://community.qlik.com/t5/Qlik-Design-Blog/Non-Gregorian-calendars/ba-p/1471155

If the posts from the other guys did help, do not forget to give them credit and mark things using the Accept as Solution on any of the posts that helped you get things solved, so others know what worked.  

Here is the base URL for the Design Blog area too in case you wish to do some further searching, there may be some other date related posts that could be helpful, this one seemed to be the best fit.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[pbran]

Sorry about the not marking it complete. I marked both of those responses.

Thank y'all for the help!