Transform severals "same" dates on an unique date

[YD]

Hello All Qlikview and Sense users,

Could you please help me with issue? Here is my problem:

I have a date field with hours and minutes with severals transactions per minute, for example:

DATE_WITH_HOUR - TRANSACTIONS 

18/06/2019 11:00 -  Transaction 1 

18/06/2019 11:01 - Transaction 2

18/06/2019 11:02 - Transaction 

19/06/2019 11:00 -  Transaction 1

19/06/2019 11:01 - Transaction 2

19/06/2019 11:02 - Transaction 3 

Etc....

I have create another field to create a calendar: 

DATE(DATE#(DATE_WITH_HOUR, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY') AS DATE_WITHOUT_HOUR

18/06/2019

18/06/2019

18/06/2019

19/06/2019

19/06/2019

19/06/2019

Etc.....

-----------------------------------------------------------------------------------------------------------------------------

I would like to have only ONE Date for the same day in the new field, then I can create the calendar and when I choose one date, I shall have all the transactions on that day

Here is the result I am looking for:

DATE WITHOUT HOUR  - DATE WITH HOUR  - TRANSACTION
18/06/2019                            18/06/2019 11:01        Transaction 1
                                                    18/06/2019 11:02      Transaction 2
                                                   18/06/2019 11:03        Transaction 3

19/06/2019                            19/06/2019 11:01       Transaction 1
                                                   19/06/2019 11:02       Transaction 2
                                                   19/06/2019 11:03        Transaction 3

Etc.....

-----------------------------------------------------------------------------------------------------------

Thank your very much for your kind help

Dimitry

[felipedl]

They are indeed still different dates, since you didn't "cut" the time part of it, you've just formated it out of the final field, but the time portion is still there.

One solution is to round the date with:

floor(DATE_WITH_HOUR)

so every hour of day 18/06/2019 goes to one equal day.

Felipe.

[YD · Accepted solution]

Hello Felipe,

Thank you very for giving me the lead to the solution, the right function was FLOOR.

But the result was the date as number like this 41852 (Date in number format)

Here is the whole function:

LOAD

DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm' ) AS %DATE,
//DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY') AS CALENDAR_DATE,
DATE(FLOOR(DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY' )), 'DD/MM/YYYY') AS CALENDAR_DATE

FROM .......path

So thank you for your kind help