Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kshitijas
New Contributor III

QS-Date Fields

Hello everyone,

               I have date fields like this:  May 12 2016 10:46AM

I want it in DD/MM/YYYY format and also want the timestamp.

How can i do that?

Thanks & Regards,

Kshitija

1 Solution

Accepted Solutions
MVP
MVP

Re: QS-Date Fields

I think I missed some parenthesis, can you try this:

Date(Floor(Date#(DateField, 'MMM DD YYYY hh:mmTT')), 'DD/MM/YYYY') as Date,

Time(Frac(Date#(DateField, 'MMM DD YYYY hh:mmTT'))) as Time,

8 Replies
sureshqv
Esteemed Contributor III

Re: QS-Date Fields

load *,

Date(Date#(Datefiled,' MMM DD YYYY hh:mm TT'),'DD/MM/YYYY') AS newdate ;

or

Date(Date#(Datefiled,' May 12 2016 10:46AM'),'DD/MM/YYYY') AS newdate ;


Edited:

nadeemsmarty
Contributor II

Re: QS-Date Fields

Try the date# and the date function

MVP
MVP

Re: QS-Date Fields

Try like this:


Date(Floor(Date#(DateField, 'MMM DD YYYY hh:mmTT'), 'DD/MM/YYYY') as Date,

Time(Frac(Date#(DateField, 'MMM DD YYYY hh:mmTT')) as Time,

kshitijas
New Contributor III

Re: QS-Date Fields

Hello,

     So here is the format pf the date i have and i want to calculate TAT of this....

Untitled4.png

kshitijas
New Contributor III

Re: QS-Date Fields

Hello ,

I tried theb formula but its coming blank...

MVP
MVP

Re: QS-Date Fields

I think I missed some parenthesis, can you try this:

Date(Floor(Date#(DateField, 'MMM DD YYYY hh:mmTT')), 'DD/MM/YYYY') as Date,

Time(Frac(Date#(DateField, 'MMM DD YYYY hh:mmTT'))) as Time,

kshitijas
New Contributor III

Re: QS-Date Fields

Hello Sunny,

     It worked thankyou. Can you also suggest how to calculate TAT(turn around time) ?? ie. difference between the review and entry date??

MVP
MVP

Re: QS-Date Fields

May be like this:

LOAD Interval([REVIEW DATE] - [ENTRY DATE]) as TAT

LOAD [ENTRY DATE],

          Date(Floor(Date#([REVIEW DATE], 'MMM DD YYYY hh:mmTT')), 'DD/MM/YYYY') as [REVIEW DATE],

          Time(Frac(Date#([REVIEW DATE], 'MMM DD YYYY hh:mmTT'))) as [REVIEW Time]

From Source;