6 Replies Latest reply: Feb 3, 2017 11:18 PM by Michael Squirrell RSS

    Summarising the output of an expression

    Michael Squirrell

      Dear all,

      I have a large table of time stamps that I wish to analyse to find the average time it takes to complete certain actions.

       

      The table could be summarized as follows:

      INSTANCEID - A key that links all rows in the table related to a specific transation

      OP_NAME - The type of operation

      CLOSED_DATE_TIME - Timestamp

       

      A small data sample might look like this:

         

      timerelease2.PNG

       

       

      I have written an expression as per below that gives me a list of intervals between two timestamps from the currently selected data, as below:

      =Interval(Only({$<OP_NAME={'Assess Registered (direct)'}>}CLOSED_DATE_TIME))-(Only({$<OP_NAME={'Validate'}>}CLOSED_DATE_TIME))

       

      If I put this expression in a Simple Chart using INSTANCEID as a dimension, I get a correct list of intervals across the selected data. Furthermore, I can Select 'Average of rows' in the Expression tab which gives me the average of the intervals shows, which is exactly the result I require.

       

      timerelease.PNG

      My question related to displaying the information.

      I just want to display one number, the average of all the selected Timestamp Intervals, in this case 30:31:31, but not display the actual data.

      How do I achieve this? I am assuming that I need to use a combination of Avg, Aggr, and Only functions around the expression shown above to achieve this but I am getting confused.

      Can anyone point me in the right direction please?