topic Re: Date Conversion From Numerical Value in Connectivity & Data Prep

Date Conversion From Numerical Value

AMorella — Thu, 16 May 2024 15:54:32 GMT

How can you convert the numerical date/time value to just the year?

I'm uploading data for a historical view of activity and am trying to extract the year from the shipment date.

The shipment date is being imported as the numerical value, instead of the actual date. The excel file has this field in date format. I attempted to remove the time stamp but the time value is apparently still there.

I've tried to convert to date when loading the data with: Year(Date#("SHIP DATE", 'MM/DD/YYYY')) As "Ship Year" but this produces no result. Screen capture below:

Re: Date Conversion From Numerical Value

rodrigo_martins — Thu, 16 May 2024 16:44:15 GMT

Have you tried

Year(Date("SHIP DATE", 'MM/DD/YYYY')) As "Ship Year"

Without the "#"?

If you want to know a bit more about the difference: link

Re: Date Conversion From Numerical Value

AMorella — Thu, 16 May 2024 17:06:37 GMT

Unfortunately, that doesn't produce any result, either.

Re: Date Conversion From Numerical Value

marksouzacosta — Thu, 16 May 2024 18:01:39 GMT

[Dates]:
Load 
[SHIP DATE],
Year([SHIP DATE]) AS YEAR,
Date([SHIP DATE]) AS [SHIP DATE FORMATTED]
Inline [
SHIP DATE
43454.817361111
44102.817361111
44176.859722222
44060.761111111
];

Re: Date Conversion From Numerical Value

AMorella — Thu, 16 May 2024 18:21:25 GMT

Unfortunately, that doesn't work, either. I'm wondering if there's something wrong with the excel spreadsheet format that's forcing a bad result. I've pulled in Date/Time fields before without issue.

It was originally a Date/Time field. I cut the time element from the field and it's formatted as a date...but only puts the numerical values shown; and it doesn't let me calculate the year.

I also tried to force the year by creating a Column in the spreadsheet, but this doesn't produce a value, either.

Re: Date Conversion From Numerical Value

marksouzacosta — Thu, 16 May 2024 18:57:03 GMT

Can you share the spreadsheet file?

Re: Date Conversion From Numerical Value

BrunPierre — Fri, 17 May 2024 06:19:41 GMT

Maybe this.

Year(Floor("SHIP DATE")) as "Ship Year"

Re: Date Conversion From Numerical Value

rodrigo_martins — Fri, 17 May 2024 12:16:23 GMT

What could be happening here is just a formatting issue. In Qlik's initial script, some culture variables are defined, such as date and time formats, decimal and thousands separators, money masks, month names, among others.

If your script is in a culture where the decimal separator is the comma (like pt-br and unlike en-us), you will face this behavior.

The solution would be an expression like this:

=Year(Date(Num#([SHIP DATE],'###','.',',')))

You can apply this expression on the script, creating a new field, or directly on the visualization. Both should work.