Calculated field with hourly ranges
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
The same question
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:
In Microsoft SQL Server, you can accomplish this by doing DATEPART(HOUR,(yourSchemaName.yourTableName):
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:
Hopefully this is what you're looking for. Please let me know if you have any other questions.
Regards,
Mike
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:
In Microsoft SQL Server, you can accomplish this by doing DATEPART(HOUR,(yourSchemaName.yourTableName):
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:
Hopefully this is what you're looking for. Please let me know if you have any other questions.
Regards,
Mike
Thanks a lot, Mike! Really appreciate it!
All the best,
Radoslav
Thanks a lot, Mike! Really appreciate it!
All the best,
Radoslav
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
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
Replies have been locked on this page!