Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

evansabres
Valued Contributor

Date Field

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.

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Date Field

Try this?

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

12 Replies

Re: Date Field

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

evansabres
Valued Contributor

Re: Date Field

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

pradosh_thakur
Honored Contributor II

Re: Date Field

Did you try the floor()  function?

Learning never stops.
evansabres
Valued Contributor

Re: Date Field

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

pradosh_thakur
Honored Contributor II

Re: Date Field

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

Learning never stops.
evansabres
Valued Contributor

Re: Date Field

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

pradosh_thakur
Honored Contributor II

Re: Date Field

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

Learning never stops.
evansabres
Valued Contributor

Re: Date Field

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

-

-

-

-

-

pradosh_thakur
Honored Contributor II

Re: Date Field

How much  is the information density?

Learning never stops.