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

[liamcameron]

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....

[DRay]

Hi @liamcameron,

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

[AssafHanina]

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): 

1. For Live Models - Calculate HoursDiff as a new column in the source database.
2. 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 
3. 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