I am importing data that includes date-time data, as follows:
[start] is a date-time with formatting 'DD/MM/YYYY hh:mm:ss'
I sense that QLIK Sense "sort of" recognizes this as a date, but not totally.
I want to make a bar chart with this data, with the date along the x-axis (as the dimension), and the count of [KEY] as the measure.
So I put the expression: =date(daystart([MALN.start],'DD/MM/YY')) for the x-axis.
And it just gives me a null value for date "-" - so all entries in the table show up counted for "-" date.
I've tried everything I could find in the QLIK Community for bringing the [start] in as a date (i.e., date#(start, 'DD/MM/YYYY hh:mm:ss')), but I am at a loss. If anyone can help out at all, or if you have any ideas, I would be so appreciative.
Solved! Go to Solution.
Excel is probably changing the format of the dates based on ts own date recognition. For the CSV file, you need to check the original date format using a text editor (notepad will do if you have nothing else) and change your interpretation to match that format.
Thanks for the correction.
Here's the thing - I'm pulling the data in from a .csv file.
When I import the data into a .xls file and then import the .xls into QLIK, Sense is able to read the date fine using that formula (as normal, I've done this hundreds of times)...
But QLIK still won't seem to recognize the date when it pulls it from the raw .csv file.
I tried to use interpretation for my dates, but it doesn't work at all. It's really strange, because if I import the .csv to .xls then it works. Is there a reason it might not work as .csv dates??
SET DateFormat='DD/MM/YYYY'; // You can set whatever format you want-date or timestamp
Load * ,
Date(MakeDate(Year([start]),Month([start]),Day([start]),'$(DateFormat)') as StartModifiedDate