1 Reply Latest reply on Feb 9, 2016 10:31 AM by Yuriy Fal

    How to calculate cycle time with aggregations from history objects with timestamps

    Alex Southcott


      I am trying to solve for a cycle time calculation, where I have a join from 2 objects - a quote-proposal "header", and a quote-proposal "history" object.

      The header contains all the info that carries across all lines for a particular quote - e.g. customer, created date, salesperson, country etc. The history object contains a timestamp and a quote stage, as well as a couple of other items.

      A quote will progress from "draft" to "pending approval" to "approved" to "presented to customer", and may go back to a previous stage.

      I can successfully solve for timestamp of first instance of "pending approval" - first timestamp of any status through table calcs.

      However, I want to be able to aggregate this measure as a median or average across a variety of dimensions (e.g. view cycle time performance by salesperson, or country or product etc). This is where I have a range of issues where the partitioning goes funny, or I get messages saying I can't aggregate a previously aggregated measure etc...

      I have also tried using LOD calcs - but am told that table calc values can't be used in LOD.


      So - my broad question is - how does someone solve for this, ideally where there is a measure created that can be dragged into future analyses without recasting table calcs?


      In the example below:

      Proposal ID sits in the header object, New value and created date come from the history object.

      Q-00070115 should be 4:22:38 - 4:16:20 = 6.3 mins

      70116 should be 4:29:08 - 4:22:33 = 6.583 mins

      70117 should be 4:29:32 - 4:25:03 = 4.483 mins


      so median = 6.3, average = 5.789 mins


      Thanking you in advance for an insight that can be given.