Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Reaction-/Resolution time calculations (with a twist)

Hi,   I'm trying to get reaction-/resolutiontime calculations from data from our ticket system formed like this:   Ticket ID LineId Ticket created Ticket modified 38 72 2010-06-02 20:3...
Need Help
austinsense's avatar
austinsense
Icon for Impactful Individual rankImpactful Individual
9 years ago

Just want to check that 1) you're making a measure and not a calculated column, 2) your date columns are set as dates, & 3) don't use tablename use the actual name of whatever table you have in your model. In your report you should insert the ticket id and the measure we just created.  Also if you copied in the measure, try typing it in instead to make sure all the pieces align to your model.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

1: Yes I'm creating measure. Actually also tested as calculated column which will result in circular dependencies error.

 

2: Columns are set as date/time in source.

 

3: Also using actual names for sure as otherwise function would return an error which it doesn't now

 

I tested further and if I create a table in my report with ID and these measures as a value Im getting following data:

 

ID  -       Reaction Time     -  Resolution Time

38  -  30.12.1899 0:00:00   -   14.1.1900 15:26:21

39   -  30.12.1899 0:00:00  -  14.1.1900 14:46:49

.

.

 

So it is calculating something but gives weird results.

 

Any ideas?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Figured out something!

     

    The first modified timestamp is always the same as ticket creation date. So the function always give 0 seconds for reaction time. The timestamp which is the same as "ticket created" timestamp needs to be ignored in function.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Would datediff work in this scenario? I have used it before but that modified timestamp and distincting ticket ID:s gives me headache.

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Anonymous

         

        Hi, try with these measures.

         

        ReactionTime =
DATEDIFF (
    MIN ( Table1[Ticket created] ),
    CALCULATE (
        MIN ( Table1[Ticket modified] ),
        FILTER ( Table1, Table1[Ticket modified] <> MIN ( Table1[Ticket created] ) )
    ),
    HOUR
)
        ResolutionTime =
DATEDIFF (
    MIN ( Table1[Ticket created] ),
    CALCULATE (
        MAX ( Table1[Ticket modified] ),
        FILTER ( Table1, Table1[Ticket modified] <> MIN ( Table1[Ticket created] ) )
    ),
    HOUR
)