Get datetime difference in hours in BEX Query Designer

[former_member613963]

Hi Experts:

In an embedded context i built a CDS View(PM Data) and consume it from a BEX query.

Dates are in format DD.MM.YYYY

Times are in format HH:MM:SS

My goal is to get datetime difference in hours as follows.

For example the calculation for Equipment A is (12/09/2019 18:00:00 - 08/09/2019 14:00:00 ) *24 = 100 Hours.

In order to do that i set up 5 formulas in query designer

as follows.

But my calculation fails (see second equipment). The End Time should be 11:00:00 instead of 18:00:00

How can i solve it?. Any help would be preciated.

Thanks in advance.

Francisco

[shasan]

Hi Juan,

Can you just try with only Equipment as your reference characteristic.

[former_member613963 · Accepted solution]

The solution was to create another formula with KEY figure = End Date(Previous formula), with exception aggregation (Last value) on characteristic End Date.

[former_member613963]

Hi Shadab.

Setting up in the formula Equipment as the only reference characteristics didn't work. Thanks anyways.

Juan Francisco