Creating Custom Time Buckets in Elasticube

[intapiuser]

Introduction

This article explains how to create a custom field which rounds timestamps (Date-Time) up into time buckets.  The buckets outlined in this article include every hour on the hour (10:00 AM, 1:00 PM), every hour on the half hour (11:30 AM, 3:30 PM), and every half hour (11:30 AM, 12:00 PM).  The included scripts will round up, but can easily be modified to round down.

Purpose/Benefit

Time buckets allow the users to see time data aggregated into buckets. Eg. One would like to see a bar chart of sales broken out by time of day. If all you have is the timestamp of each transaction, you will not be able to plot time of day along the x-axis, since there will likely be far too many values

Steps

Step 1 - Open Elasticube and select applicable timestamp field

In our example, we are starting with a table containing timestamps with some additional transactional data.

Step 2 - Add custom field

Click the Add new custom field button on the table you wish to add the buckets to.

Step 3 - Name the field and select the expression

Click on the new field you just created. We renamed ours 'TimeBuckets'. Then click the Edit expression button.

In the dialog box that opens, paste in one of the following scripts based on the type of time buckets that you want to see (specifics in comments in first line of each script):

--rounds up to next hour on the hour (9:00 AM, 1:00 PM) -- eg. 8:16 AM rounds to 9:00 AM, 12:45 PM rounds to 1:00 PM, etc.
CASE
WHEN GetHour([timestamp]) + 1 = 12 THEN ToString(GetHour([timestamp]) + 1) + ':00 PM'
WHEN GetHour([timestamp]) + 1 >= 12 THEN ToString(GetHour([timestamp]) - 11) + ':00 PM'
ELSE ToString(GetHour([timestamp]) + 1) + ':00 AM'
END

--rounds up to next hour on the half hour (8:30 AM, 1:30 PM) -- eg. 8:16 AM rounds to 8:30 AM, 12:45 PM and 1:06 PM round to 1:30 PM, etc.
CASE
WHEN GetHour([timestamp]) + Round(GetMinute([timestamp]) / 60.0,0) > 12 THEN ToString(GetHour([timestamp]) + Round(GetMinute([timestamp]) / 60.0,0) - 12) + ':30 PM'
ELSE ToString(GetHour([timestamp]) + Round(GetMinute([timestamp]) / 60.0,0)) + ':30 AM'
END

--rounds up to the next half hour (8:30 AM, 2:00 PM) -- eg. 8:16 AM rounds to 8:30 AM, 12:45 PM rounds to 1:00 PM and 1:06 rounds to 1:30 PM, etc.
CASE
WHEN GetHour([timestamp]) + Round(GetMinute([timestamp]) / 60.0,0) > 12 THEN ToString(Round(GetMinute([timestamp]) / 60.0,0) + GetHour([timestamp]) -12)
ELSE ToString(Round(GetMinute([timestamp]) / 60.0,0) + GetHour([timestamp]))
END
+ ':'
+ Case
WHEN GetHour([timestamp]) + Round(GetMinute([timestamp]) / 60.0,0) < 12 AND Round(GetMinute([timestamp]) / 60.0,0) = 1 THEN '00 AM'
WHEN GetHour([timestamp]) + Round(GetMinute([timestamp]) / 60.0,0) < 12 AND Round(GetMinute([timestamp]) / 60.0,0) = 0 THEN '30 AM'
WHEN GetHour([timestamp]) + Round(GetMinute([timestamp]) / 60.0,0) >= 12 AND Round(GetMinute([timestamp]) / 60.0,0) = 1 THEN '00 PM'
ELSE '30 PM'
END

Step 4 - Save Expression and build Elasticube

Once you have saved the expression and built the Elasticube, your new time buckets will be ready to use!

Enjoy!