Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
ekaponkratova
Contributor

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

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 )

7 Replies
ekaponkratova
Contributor

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

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

MVP & Luminary
MVP & Luminary

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

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

ekaponkratova
Contributor

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

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

ekaponkratova
Contributor

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

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);

ekaponkratova
Contributor

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

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

ekaponkratova
Contributor

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

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.

ekaponkratova
Contributor

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

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?