Formatting date/time data

Answered

Follow

Jane P

March 28, 2017 15:59

Hi all,

I have a request to sort data by minutes or divide an hour into blocks, let's say to have three 20-minute blocks in each hour. We want to know how many visitors finish a certain set of actions within the first, second or third part of the hour. 

Any ideas? 

J ~~

1

• 

  Michael Becker March 28, 2017 17:14

  Hi Jane,

  you would need to do two things on your ElastiCube. On table holding your date-time field add an additional field of type integer and in it use following function GetMinute([date-time field]); let's assume this field will be called Field1. Then add a second field (text field) which will have following syntax:

  CASE

  WHEN Field1 >=0 and Field1 <20 THEN 'First 20 minutes'

  WHEN Field1 >=20 AND Field1 <40 THEN 'Middle 20 minutes'

  WHEN Field1 >=40 and Field1 <=59 THEN 'Last 20 minutes'

  ELSE 'No time'

  END

  That should give you a text field which will allow you to group data by 20 minutes blocks within each hour. Hope it helps.

  Regards,

  Michał

  1

  Comment actions Permalink
• 

  Jane P April 07, 2017 16:02

  Thank you Michal.I'll try this approach. Do you know if there is a way to do this in a dashboard?

  0

  Comment actions Permalink

Please sign in to leave a comment.