Date Conversion From Numerical Value

[AMorella]

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:

[rodrigo_martins]

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

[AMorella]

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

[marksouzacosta]

[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
];

[AMorella]

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.

[marksouzacosta]

Can you share the spreadsheet file?

[BrunPierre]

Maybe this.

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

[rodrigo_martins]

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.