Dimension/Measures for Durations (HH:MM:SS) in SAP Analytics Cloud

[former_member269926]

Hello Community,

I work with the SAP Analytics Cloud and would like to display processing times as HH:MM:SS in my story. These times should also be calculated in regard to selected filters.

In my backend system (a data model based on ABAP CDS View with an SAP S/4Hana with a live data connection) I've created a dimension (later as key figure) "Durations in Seconds". Here I've used the function "tstmp_seconds_between" predefined in the ABAP CDS View with the corresponding time stamps available in the dataset.

Furthermore I did some type conversions to use the built-in functions DIV and MOD. With the following code I finally calculated the dimensions for HH, MM and SS:

When you preview the ABAP CDS View, you get the following result:

The dimensions HH, MM and SS are calculated correctly from the number of seconds. The model behaves as expected.

I converted dimensions HH, MM and SS into key figures in the cube and query views:

If you look at the result in the SAP Analytics Cloud, you unfortunately get wrongly calculated values. It looks as if, among other things, the values for HH were not calculated correctly.

If you look at the above values "46 * 60 + 540", you get "3300". It is at least partially comprehensible. If we look again at the individual items (in SAC), they are also correct:

I suspect that the calculation must actually take place in the query. Possibly with annotation of the form "@AnalyticsDetails.query.formula", but to my knowledge the modulo operator is not supported, right?

The modulo operator is not present in the SAC either, is it not? As a reminder, this is a live data connection.

I am very grateful for hints or alternative solutions.

Thank you!

Regards,

Thorsten

[former_member133066]

Hey thorsten.wuelpern2, has been some time that you post your question, but here is the answer related on how to aggregate the hours.

The first part was converting to tstmp then you can calculate the difference between initial/final date/time, after that we can use division to get the hours in decimals to aggregate correctly.

Here is the piece of code related to your question:
*Plus if you want to add an unit next to the aggregation, you can use the logic below the comment "--Unit (HH)".

                                                                                                                         as Equipment,
       @UI.hidden: true
       _EquipmentText[1: Language = $session.system_language ].EquipmentName                                                                     as EquipmentName,
       _Equipment.TechnicalObjectType                                                                                                            as EquipmentType,
       qmel.qmart                                                                                                                                as NotificationType,
       qmel.qmtxt                                                                                                                                as NotificationText,
       --Unit (HH)
       @Semantics.unitOfMeasure: true
       qmih.maueh                                                                                                                                as MaintObjDowntimeDurationUnit,

       --Required Date
       qmel.strmn                                                                                                                                as RequiredStartDate,
       qmel.strur                                                                                                                                as RequiredStartTime,
       qmel.ltrmn                                                                                                                                as RequiredEndDate,
       qmel.ltrur                                                                                                                                as RequiredEndTime,

       CONCAT_WITH_SPACE(CONCAT(CONCAT(Concat(substring(qmel.strmn, 7, 2), '/' ) , CONCAT(substring(qmel.strmn, 5, 2) , '/')), substring(qmel.strmn, 1, 4)),
       CONCAT(CONCAT(Concat(substring(qmel.strur, 1, 2), ':' ) , CONCAT(substring(qmel.strur, 3, 2) , ':')), substring(qmel.strur, 4, 2)), 1)    as ReqStartDateTime,

       CONCAT_WITH_SPACE(CONCAT(CONCAT(Concat(substring(qmel.ltrmn, 7, 2), '/' ) , CONCAT(substring(qmel.ltrmn, 5, 2) , '/')), substring(qmel.ltrmn, 1, 4)),
       CONCAT(CONCAT(Concat(substring(qmel.ltrur, 1, 2), ':' ) , CONCAT(substring(qmel.ltrur, 3, 2) , ':')), substring(qmel.ltrur, 4, 2)), 1)    as ReqEndDateTime,

       case
         when ( qmel.strmn != '00000000' and qmel.strur != '000000' )  and
              ( qmel.ltrmn != '00000000' and qmel.ltrur != '000000' )
           then
            CONCAT(CONCAT(LPAD(cast(cast(DIV(tstmp_seconds_between( dats_tims_to_tstmp( qmel.strmn, qmel.strur ,
            abap_system_timezone( $session.client,'NULL' ),
            $session.client,
            'NULL' ),  dats_tims_to_tstmp( qmel.ltrmn, qmel.ltrur ,
            abap_system_timezone( $session.client,'NULL' ),
            $session.client,
            'NULL' ), 'FAIL' ), 3600 ) as abap.int4 ) as abap.char(25)), 2, '0'), ':' )          ,

            CONCAT(CONCAT(LPAD(cast(DIV(MOD(CEIL(tstmp_seconds_between( dats_tims_to_tstmp( qmel.strmn, qmel.strur ,
            abap_system_timezone( $session.client,'NULL' ),
            $session.client,
            'NULL' ),  dats_tims_to_tstmp( qmel.ltrmn, qmel.ltrur ,
            abap_system_timezone( $session.client,'NULL' ),
            $session.client,
            'NULL' ), 'FAIL' ) ), 3600 ), 60)  as abap.char(25)), 2, '0'), ':' ) ,

            LPAD(cast(MOD(MOD(CEIL(tstmp_seconds_between( dats_tims_to_tstmp( qmel.strmn, qmel.strur ,
            abap_system_timezone( $session.client,'NULL' ),
            $session.client,
            'NULL' ),  dats_tims_to_tstmp( qmel.ltrmn, qmel.ltrur ,
            abap_system_timezone( $session.client,'NULL' ),
            $session.client,
            'NULL' ), 'FAIL' ) ), 3600 ), 60)  as abap.char(25)), 2, '0')))
         else 'N/A'
       end                                                                                                                                       as ReqDifference,

       @Aggregation.default:#SUM
       @Semantics.quantity.unitOfMeasure: 'MaintObjDowntimeDurationUnit'
       case
         when ( qmel.strmn != '00000000' and qmel.strur != '000000' ) and
              ( qmel.ltrmn != '00000000' and qmel.ltrur != '000000' )
            then
               division(tstmp_seconds_between( dats_tims_to_tstmp( qmel.strmn, qmel.strur,
                   abap_system_timezone( $session.client,'NULL' ),
                   $session.client,
                  'NULL' ),  dats_tims_to_tstmp( qmel.ltrmn, qmel.ltrur ,
                   abap_system_timezone( $session.client,'NULL' ),
                   $session.client,
                  'NULL' ), 'FAIL' ), 3600, 2)
         else 0.00
       end                                                                                                                                       as ReqDifferenceInHours,

The aggregation should look like this: