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.
Date(Floor (Date#( start, 'DD/MM/YYYY hh:mm:ss')) , 'DD/MM/YYYY')
- Date#() is used for reading a date format
- Date() is used for defining the internal date format for display.
What you're saying is "Read the Date in this format, Floor() it to get rid of the time, then display it in this other format"
(You might not want to get rid of the time-part. Timestamp#() will work in a similar fashion with timestamps).
You're a superstar - this solved my problem! I was looking at the csv file in Excel, and using the date#() function with the format being displayed there. Once I took a look at the data in Notepad++...figured out it came out a little differently. So corrected it to match the .csv format, and voila! It works now!
Thanks so much for your help!
I would Create a New Section to the Load Script
So I would add something like :
DECLARE FIELD DEFINITION TAGGED '$date'
first_month_of_year = 1
Year($1) As Year Tagged '$year',
MonthName($1) as Month Tagged '$month',
QuarterName($1) as Quarter Tagged '$quarter',
Date($1) as Date Tagged ('$date', '$day'),
Date(daystart($1)) as Day Tagged ('$date', '$day'),
WeekStart($1) as Week Tagged '$week',
Weekday($1) as Weekday Tagged '$weekday',
DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');
DERIVE FIELDS FROM EXPLICIT TAGS '$date' USING Calendar;
this will then split "start" into a number of fields to choose from so it will make it split into
this makes it alot easier to make tables because than you can do it by month, Week, Year, ect it should work for CSV files as well.
hope this helps