HDIFF in Formulas when I can only group by Days...

Help and How-To

I have two dates and i want to calculate the hours between... sisense says to use HDIFF

Hour Difference HDiff( <Start Time>, <End
Time> )
Returns the difference between <Start Time> and <End Time> in
hours.

But when selecting a date field from the data selector, i can only group in years,months,days, or aggregative time...

here's a screenshot of an hour diff, where the formula is all 0....

liamcameron

Posted 1 year ago·Last reply 1 year ago

3 comments

sahmed

·1 year ago

Just to get one column, changing the whole model is way too much challenging. I wish we had Hdiff at dashboard level which could actually work.

Assaf Hanina

·1 year ago

hey liamcameron ,

Applying those type of calculations can be challenging in the dashboard level.
normally the Hours Difference is calculation require for each row but the presentation needed is to group the values per different Dimensions.

The suggested approach for those cases is to apply the Calculation directly in the Source Data (Live/Elasticube), which can provide the following main advantages.

Minimize computation at the dashboard level since multiple users will run this query, while it can be applied once in the source database.
Support all types of Widgets.
Allow easy grouping by various dimensions.

In case the 2 columns are taken from the same Table or can be combined into a single table,
For example: Start_time and End_time, than Suggest to add additional column for HoursDiffcalculation.

Implementation Suggested (Assumption of the date columns are in the same table):

For Live Models - Calculate HoursDiff as a new column in the source database.
For Elasticube - Multiple options:
- Calculate HoursDiff as a new column in the source database.
- perform the HoursDiffcalculation throguht Import Query
- Perform HoursDiffas with custom Column
in case needed to combine multiple tables into one:
- use custom table
- add to the select statement the HoursDiff calculation
Please find related Documentation

Best Regards

DRay

·1 year ago

Hi liamcameron,

Thank you for reaching out. Are you able to attach the screenshot? I don't see it.