Date-Time formatting

[#1]

Hi all,

I am importing data that includes date-time data, as follows:

Qualify *;

[MALN]:

LOAD  [submissiondate],

[start]

...

[KEY]

FROM ...

[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.

Many thanks,

Janna

[sunny_talwar]

Try this:

=Date(DayStart([MALN.start]),'DD/MM/YY')

or

=Date(Floor([MALN.start]), 'DD/MM/YY')

[#3]

Hi Sunny,

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.

Any suggestions?

Thanks again,

Janna

[its_anandrjs]

Try this as calculated dimension

=Daystart(Date([MALN.start],'DD/MM/YY'))

1. First convert to date format

2. Then use DayStart for the finding Daystart

Regards

Anand

[sunny_talwar]

You can use Date#(), TimeStamp#() Time#() function to help QlikSense interpret your datefields. (Why don’t my dates work?)

[reddy-s]

Hi Janna,

Did you check if Qlik is interpreting the field as a date field? If so this has to addressed first.

Thanks,

Sangram.

[#7]

Hi Sunny,

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??

Thanks,

Janna

[sunny_talwar]

Can you share a row from your csv in the csv format so that we can test it out?

[jagan]

HI,

Try like this

Date(Floor(DateFieldName),  'DD/MM/YYYY')

OR

Date(Floor(TimeStamp#(DateFieldName, 'DD/MM/YYYY  hh:mm:ss')), 'DD/MM/YYYY')

Hope this helps you.

Regards,

Jagan.

[brijesh1991]

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

From Table...........