topic Removing Time Stamp and Calculating Estimated Departure - QlikSense in App Development

Removing Time Stamp and Calculating Estimated Departure - QlikSense

Anonymous — Thu, 05 May 2016 17:52:09 GMT

Hello - I am fairly advanced in QlikView but I have just started using QlikSense, so I am struggling with some of the limitations.

First, I have a date field formatted as:

02/29/2016 10:00AM

I would like to remove the timestamp and just format as MM/DD/YYYY, but nothing I have tried seems to be working.

Also, I have a "Length of Stay" field, which determines the length of a clients trip. I would like to add "Length of Stay" to the date field mentioned above to get an Estimated Date of Departure.

i.e

Date field = 05/05/2016

Length of Stay = 5

Calculated Field, Estimated Date of Departure = 05/10/2016

Thanks in advance,

- dave

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

sunny_talwar — Thu, 05 May 2016 17:55:09 GMT

How about these

Date(Floor(TimeStamp('02/29/2016 10:00AM', 'MM/DD/YYYY hh:mmTT'))) as Date

Date(Floor(TimeStamp('02/29/2016 10:00AM', 'MM/DD/YYYY hh:mmTT')) + [Length of Stay]) as DEPARTURE_DATE

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

Anonymous — Thu, 05 May 2016 18:04:17 GMT

Sunny - Its not working ... I feel like I am not sure if I am using the right quotations or brackets in the calculation:

LOAD

"Start Date",

Date(Floor(TimeStamp('Start Date', 'MM/DD/YYYY hh:mmTT')))

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

sunny_talwar — Thu, 05 May 2016 18:06:48 GMT

You are using TimeStamp or TimeStamp#? If timestamp# is still not working, can you try this:

LOAD

"Start Date",

Date(Date#(Left(Trim([Start Date]), 10), 'MM/DD/YYYY')', MM/DD/YYYY') as DATE

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

Anonymous — Thu, 05 May 2016 18:07:07 GMT

Neither of these worked either ... Im not getting a wrong answer, I am just getting nothing in those fields

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

sunny_talwar — Thu, 05 May 2016 18:11:00 GMT

Check my response above

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

Anonymous — Thu, 05 May 2016 18:13:33 GMT

Error in expression: ')' expected

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

sunny_talwar — Thu, 05 May 2016 18:14:39 GMT

May bad, try this:

LOAD

"Start Date",

Date(Date#(Left(Trim([Start Date]), 10), 'MM/DD/YYYY'), 'MM/DD/YYYY') as DATE

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

Anonymous — Thu, 05 May 2016 18:16:39 GMT

Nice Sunny. That fixed the timestamp issue ... Now how do I incorporate that into the Est Dept Date calc?

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

sunny_talwar — Thu, 05 May 2016 18:18:31 GMT

How about this:

LOAD Date(DATE + [Length of Stay], 'MM/DD/YYYY') as DEPARTURE_DATE

LOAD

"Start Date",

Date(Date#(Left(Trim([Start Date]), 10), 'MM/DD/YYYY'), 'MM/DD/YYYY') as DATE

Re: Removing Time Stamp and Calculating Estimated Departure - QlikSense

Anonymous — Thu, 05 May 2016 18:24:11 GMT

Great job Sunny. You just made my day. Thanks very much per usual.