Calculated field with hourly ranges

Radoslav Georgiev shared this question 5 years ago
Answered

Hello,

I have an interesting requirement that I am not sure how I can cover and it it's doable altogether in Yellowfin. Please see below:

"Would it be possible to add custom fields to X Custom view showing what hour a given date field falls into ie todays date 19/09/2018 13:30 would fall into 13:00 - 13:59.

This is to allow for trending by hourly time bands across submission time, modified time, resolution time, etc....."

As a workaround i tried to edit an existing field's format and only leave the hour (ie 1 PM), but even if it looks good it's not something that can be aggregated and the latter is the goal.

Please help :)


All the best, Radoslav

Replies (3)

photo
1

Hi Radoslav,

Thanks for reaching out. This should be possible via Freehand SQL in a Calculated Field, though the ultimate scripting of which is dependent on your RDBMS the View is built on.

Head into your view and create a Calculated Field:

/+GqaBYQDbyPAAAAAElFTkSuQmCC

In Microsoft SQL Server, you can accomplish this by doing DATEPART(HOUR,(yourSchemaName.yourTableName):

/P6mFTFlQk9RSkxkCCCCAAAIIIIAAAgggEBdIyfQztBFAAAEEEEAAAQQQQACBiRIgqJkoWfJFAAEEEEAAAQQQQACBlAgQ1KSEmZsggAACCCCAAAIIIIDARAkQ1EyULPkigAACCCCAAAIIIIBASgQIalLCzE0QQAABBBBAAAEEEEBgogT+Dz7NryTO4tyhAAAAAElFTkSuQmCC

In MySQL, you can just do 'HOUR(yourSchemaName.yourTableName)'. If you're using another RDBMS, you'll just have to do a quick search on the syntax to extract the hour from a timestamp.

Make sure you set the CF to Metric so that you can perform calculations based off the field.

Now a numerical value is assigned to each hour:

/wMMEqV4ciWiAAAAAABJRU5ErkJggg==

Hopefully this is what you're looking for. Please let me know if you have any other questions.

Regards,

Mike

photo
1

Thanks a lot, Mike! Really appreciate it!


All the best,

Radoslav

photo
1

Hi Radoslav,

You're welcome! I'll go ahead and mark this as Answered. Please don't hesitate to reach out with any further questions or concerns.

Regards,

Mike

Leave a Comment
 
Attach a file