Date(Floor.... seems to miss some dates when aggregating
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.
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);
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.