topic Re: How to convert Date&Time to Date in QlikView

How to convert Date&Time to Date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Date & Time	Status
Nov 08 2018 11:59:44	Not Hungry
Nov 08 2018 11:59:44	Not Hungry
Nov 08 2018 11:59:44	Not Hungry
Nov 09 2018 11:59:44	Not Hungry
Nov 09 2018 11:59:44	Not Hungry
Nov 08 2018 11:59:44	Not Hungry
Nov 10 2018 11:59:44	Not Hungry
Nov 10 2018 11:59:44	Not Hungry
Nov 11 2018 11:59:44	Not Hungry

1. How to convert Date & time above to Date ?

2. How to count the status ? and group by date ?

Re: How to convert Date&Time to Date

Anonymous — Mon, 10 Dec 2018 10:34:07 GMT

i am successful with question no 1 and i use Date(TimeStamp#([Date&Time],'MMM DD YYYY hh:mm:ss')) as myDate,

But still unable to do it for no 2 ?

Re: How to convert Date&Time to Date

sunny_talwar — Mon, 10 Dec 2018 12:55:46 GMT

May be use this instead for Date

Date(Floor(TimeStamp#([Date&Time],'MMM DD YYYY hh:mm:ss'))) as myDate,

For second question, try this

Dimension

myDate

Expression

Count(Status)

or

Count(DISTINCT Status)

Re: How to convert Date&Time to Date

Colin-Albert — Mon, 10 Dec 2018 13:52:29 GMT

Here are a few of useful blogs on handling dates correctly.

Why Don't my dates work https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

Get the dates right https://community.qlik.com/t5/Qlik-Design-Blog/Get-the-Dates-Right/ba-p/1476178

Date Fields - Technical briefing https://community.qlik.com/t5/QlikView-Documents/QlikView-Date-fields/ta-p/1484786

Re: How to convert Date&Time to Date

MarcoWedel — Mon, 10 Dec 2018 22:43:34 GMT

Re: How to convert Date&Time to Date

Anonymous — Tue, 11 Dec 2018 06:35:44 GMT

Hi,
I put the dimension as myDate and the expression as suggested.. tried both... it still didn't group the status under same date..

Re: How to convert Date&Time to Date

Anonymous — Tue, 11 Dec 2018 06:37:10 GMT

Attached is the screenshot .. the status is not group by date.

I want to count total number under same date in the chart....

Re: How to convert Date&Time to Date

MarcoWedel — Tue, 11 Dec 2018 07:50:58 GMT

Your Date() function only formats your timestamps as Dates but keeps the underlying distinct timestamp values.
Did you try Sunny's Date(Floor()) combination or the DayName() function?

Re: How to convert Date&Time to Date

Anonymous — Tue, 11 Dec 2018 08:12:06 GMT

Hi..

Thanks.. it is working now after i put as suggested.

Date(Floor((TimeStamp#([Date&Time],'MMM DD YYYY hh:mm:ss')))) as myDate,