topic Re: Date Field in QlikView

Date Field

evansabres — Thu, 24 May 2018 19:25:44 GMT

I have a date field called 'add_date_time' that is in a format of a timestamp from the data source like below

7/31/2017 18:10:51:026

This field is a transaction date and I want to chart sales by transaction date, the problem is that many transactions occur on a date and each transaction is time-stamped. How can I have all transactions that occur on 7/31/2017 show as that date as opposed to individually, based on time, would this be done in the script.

Re: Date Field

sunny_talwar — Thu, 24 May 2018 19:31:25 GMT

You should def. create a date field in the script like this

Date(Floor(Date#(add_date_time, 'M/D/YYYY hh:mm:ss.fff')), 'M/D/YYYY') as DateField

Now use DateField as your dimension

Re: Date Field

evansabres — Thu, 24 May 2018 19:39:05 GMT

When I enter this into the script, the script functions, however does not produce a result to use in a table or chart

Re: Date Field

pradosh_thakur — Thu, 24 May 2018 19:40:16 GMT

Did you try the floor() function?

Re: Date Field

evansabres — Thu, 24 May 2018 19:43:46 GMT

Yes, works in the script, but when i look at the data output, (in the table preview) the field only returns a '- '

Re: Date Field

pradosh_thakur — Thu, 24 May 2018 19:51:43 GMT

Sunny's expression should work just fine. Incase you have any extra space try this as well

Date(Floor(Date#(Purgechar(add_date_time, ' ') , 'MM/DD/YYYYhh:mm:ss.fff')), 'MM/DD/YYYY') as DateField

Re: Date Field

evansabres — Thu, 24 May 2018 19:58:15 GMT

Tried that again. The script works fine, however when I check the data preview table, there is no data displayed

Re: Date Field

pradosh_thakur — Thu, 24 May 2018 19:59:59 GMT

Can you please post the Screenshot of the two fields In a list box?

Re: Date Field

evansabres — Thu, 24 May 2018 20:04:56 GMT

Date(Floor(Date#(add_datetime, 'M/D/YYYY hh:mm:ss.fff')), 'M/D/YYYY') as DateField,

results show in data preview table as

DateField

Re: Date Field

pradosh_thakur — Thu, 24 May 2018 20:11:03 GMT

How much is the information density?

Re: Date Field

vishsaggi — Thu, 24 May 2018 20:14:15 GMT

Try this?

Date(Floor(Timestamp#(add_datetime, 'M/D/YYYY hh:mm:ss.fff')), 'M/D/YYYY')

Re: Date Field

pradosh_thakur — Thu, 24 May 2018 20:26:12 GMT

Time stamp #..

Re: Date Field

evansabres — Tue, 29 May 2018 14:58:10 GMT

This could very well be the correct solution. My field seems to be a bit tricky for whatever reason and is not responding to any formatting