Date(Floor.... seems to miss some dates when aggregating

[Anonymous]

Hi all,

I have this part in the script...

LOAD Date(Floor(Date(TimeStamp,'MM/DD/YYYY hh:mm:ss.ffff')),'MM/DD/YYYY') AS Date,
TimeStamp

So, if I use the Date as a dimension I get an N result, and if TimeStamp, I get another. When I was checking, it seems like when Date is used, some days got missing. It is pretty random, I didn't find any pattern. Have you had something similar? Another weird part is that it affects only last year.

The problem is that I use Date for the calendar )

[Anonymous]

And unfortunately with that extension, Date(Floor(Date(TimeStamp,'MM/DD/YYYY hh:mm:ss.ffff')),'MM/DD/YYYY') doesn't work.

[rwunderlich]

If you are trying to read the date before you apply the floor, you want to use the TimeStamp# function.  I believe your correct statement would be:

LOAD Date(Floor(TimeStamp#(TimeStamp,'MM/DD/YYYY hh:mm:ss.ffff')),'MM/DD/YYYY') AS Date,
TimeStamp

-Rob

[Anonymous]

Let me try, Rob, but is it the reason of the missing days? That I over-flored, so to speak?

[Anonymous]

In this format: Date(Floor(TimeStamp#(TimeStamp,'MM/DD/YYYY hh:mm:ss.ffff')),'MM/DD/YYYY') AS Date, The field is not recognized after the data is loaded. I am trying with the simpler version - Date(Floor(TimeStamp),'DD/MM/YYYY')

If I use it as a part of the below statement, the newly created field 'Date' doesn't seem to be connected to the TimeStamp i.e. if I select dates in the calendar - which uses Date as a dimension - it won't make impact on the figures.

Vessel:
LOAD
RowNo() as v_row_id ,
idvessl,
VISIT_VSL_LENGTH,
STOW_ACTIVITY_CODE,
VSC_BERTH,
VESSEL_VISIT_C as visit,
DEPARTURE_LAST_LINE_TM,
EST_ARV_D,
EST_DPTR_D as TimeStamp,
TEU as teu_container,
MOIS
FROM [lib://Desktop/vessel_2017.xlsx]
(ooxml, embedded labels, table is Sheet1);

TempCalendar:
LOAD
Date(Floor(TimeStamp),'DD/MM/YYYY') AS Date,
TimeStamp,
Hour(TimeStamp) AS Hour,
Minute(TimeStamp) AS Minute,
month(TimeStamp) AS Month,
year(TimeStamp) AS Year,
day(TimeStamp) as Day,
week(TimeStamp) as week;
LOAD
Timestamp($(vMinDate) + (RecNo() - 1)/1440) as TimeStamp
AUTOGENERATE 1440 * $(vDays);

[Anonymous]

Date(Floor(TimeStamp),'DD/MM/YYYY') AS Date 

seems to work, to be fully tested tomorrow as now I am off-the-premises but it will be great if someone could explain what was wrong with the option

Date(Floor(TimeStamp#(TimeStamp,'MM/DD/YYYY hh:mm:ss.ffff')),'MM/DD/YYYY') AS Date

[Anonymous]

No, it's not working. The interesting part is that if I put the calendar with Date as a dimension and Timestamp in a filter pane, when I select dates in a calendar, no selection is made in the TimeStamp filter pane which should not the case as Date derived from TimeStamp. Utterly lost.

[Anonymous]

I have a question. You said that I need to use:

LOAD Date(Floor(TimeStamp#(TimeStamp,'MM/DD/YYYY hh:mm:ss.ffff')),'MM/DD/YYYY') AS Date,

It did not work. I feel is because I need to set up the format using LET. Is it correct?