DateDiffWorkhours doesn't work properly?

Hi everyone,

i’m working on eazybi reports and i have a little problem with DateDiffWorkhours function.

I want to see how many days/hours/minutes between issue created date and issue closed date.

I use the function like this

DateDiffWorkhours(
  [Issue].CurrentHierarchyMember.get('Created at'),
  [Issue].CurrentHierarchyMember.get('Closed at'), 
  '67', 
  '8:30-18:30'
)

Formatting #.###.## Decimal

issue SC-52 output is: 34.00
issue SC-79 output is 23.18

I try to convert the outpu in a dd/hh/mm format like this

DateDiffWorkhours(
  [Issue].CurrentHierarchyMember.get('Created at'),
  [Issue].CurrentHierarchyMember.get('Closed at'), 
  '67', 
  '8:30-18:30'
) / 24 * 3600

Formatting ##d##h##m Days, hours, minutes

For SC-52 output should be 3d4h00m,
SC-79 output should be 2d3h11m

instead i have this:

Where i’m wrong? Any idea on your side ?

Thanks for your feedback !

Francesco.

Hi @Francesco_Esposito

eazyBI Duration formatting doesn’t support worktime duration and that won’t calculate datediffresults in work minutes if that is your goal.

I could recommend a workaround with calculated measures to finally calculate the duration from few other measures.

Here is the definition to my report with calcualted measures.

Martins / eazyBI

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "datediff use-case",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[created at new]","[Measures].[resolved at new]","[Measures].[Difference in full workhours]","[Measures].[Diff in full workdays]","[Measures].[workhours in difference]","[Measures].[Workminutes in difference]","[Measures].[Duration in workhours]"],"members":[{"depth":0,"full_name":"[Measures].[created at new]","format_string":"yyyy-mm-dd hh:nn:ss","report_specific":true,"name":"created at new","calculated":true},{"depth":0,"full_name":"[Measures].[resolved at new]","format_string":"yyyy-mm-dd hh:nn:ss","report_specific":true,"name":"resolved at new","calculated":true},{"depth":0,"full_name":"[Measures].[Difference in full workhours]","format_string":"#,##0.00","report_specific":true,"name":"Difference in full workhours","calculated":true},{"depth":0,"full_name":"[Measures].[Diff in full workdays]","format_string":"#,##0.00","report_specific":true,"name":"Diff in full workdays","calculated":true},{"depth":0,"full_name":"[Measures].[workhours in difference]","format_string":"#,##0.00","report_specific":true,"name":"workhours in difference","calculated":true},{"depth":0,"full_name":"[Measures].[Workminutes in difference]","format_string":"#,##0","report_specific":true,"name":"Workminutes in difference","calculated":true},{"depth":0,"full_name":"[Measures].[Duration in workhours]","format_string":"#,##0.00","report_specific":true,"name":"Duration in workhours","calculated":true}]}]},"rows":{"dimensions":[{"name":"Issue","selected_set":["[Issue].[DEMO 001].[D11-1 Delacroix's malices costliness resurface maharajah brusqueness apportionment vividness colanders integrating]"],"members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":["[Measures].[Issues created]"],"operator":"\u003e","value":"0"}]},"nonempty_crossjoin":true},"pages":{"dimensions":[]},"options":{},"view":{"current":"table","maximized":false,"table":{"show_key":["Issue"]}},"calculated_members":[{"dimension":"Measures","name":"Workminutes in difference","formula":"Int(\n\n[Measures].[Difference in full workhours]*60 \n- \n([Measures].[Diff in full workdays] *10*60)\n-\n([Measures].[workhours in difference]*60)\n\n)\n","format_string":""},{"dimension":"Measures","name":"Duration in workhours","formula":"IIf(\n[Measures].[Difference in full workhours] \u003e 0,\nCast([Measures].[Diff in full workdays] as string) ||\"wd \"\n|| Cast([Measures].[workhours in difference] as string) ||\"h \"\n|| \n\nCASE WHEN\n[Measures].[Round week minutes]\u003e0\nTHEN\nCast([Measures].[Round week minutes] as string) \nELSE\nCast(0 as string) \nEND\n\n\n||\"m \"\n\n\n,\n\"\")","format_string":""},{"dimension":"Measures","name":"workhours in difference","formula":"Int(\n\n[Measures].[Difference in full workhours] - \n([Measures].[Diff in full workdays] *10)\n\n)\n","format_string":"#,##0.00"},{"dimension":"Measures","name":"Diff in full workdays","formula":"Int(\nDateDiffWorkdays(\n  [Measures].[created at new],\n  [Measures].[resolved at new]\n)\n)","format_string":"#,##0.00"},{"dimension":"Measures","name":"Difference in full workhours","formula":"DateDiffWorkhours(\n  [Measures].[created at new],\n  [Measures].[resolved at new], \n  '67', \n  '8:30-18:30'\n) ","format_string":"#,##0.00"},{"dimension":"Measures","name":"resolved at new","formula":"DateParse('2023-06-14 15:29:00')","format_string":"yyyy-mm-dd hh:nn:ss"},{"dimension":"Measures","name":"created at new","formula":"DateParse('2023-06-09 11:29:00')","format_string":"yyyy-mm-dd hh:nn:ss"}]}
  } ],
  "calculated_members": []
}

Thx a lot for your help!