3 Replies · Latest reply on Jan 2, 2018 10:37 AM by Adam Stout

    Script to both 'Count Distinct' & calculate 'Average Age'

      Hey everyone,


      Let me start by saying Im not a developer but that Im just dangerous enough to get by and understand basic coding concepts.


      I ran into a situation where I need to count distinct records while also calculating those records average age (Current Date/Time - Opened Date/Time).  This is needed because I'm coming from a referenced Alert table that has a many to many relationship with INC records.


      Basically I need a combined version of the below 2 screenshots.


      What I would use to count distinct records



      The below is what I would use to calculate average age of an INC


      The script is pretty basic :

      var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};

      var days=function(x,y){return diff(x,y)/(24*60*60*1000);};

      days(current.incident.opened_at, score_end);



      Can someone at least help point me in the right direction ? My gut tells me this is an array type situation.

        • Re: Script to both 'Count Distinct' & calculate 'Average Age'
          Adam Stout

          Your description is leaving some important background.  There are often multiple ways of doing things but you have already started down a path that may not be the best.  Can you add more description of what you are trying to achieve with these indicators?


          Can you use an indicator on the incident table and breakdown by what you need (what you are doing the counts by).  Then this should be straightforward as two indicators and will most likely produce more relevant records in the records tab (if you are keeping snapshots for it).


          If you have to do it this way, I think you can do this by creating a script for the base table (not shown in your example where you are COUNTing DISTINCT).  In that script use a glide aggregate with a group by on incident and incident.opened_at.  Then you can loop through these and get the value you need.  Since you are distincting, be sure to use GlideAggreagate and not GlideRecord as the performance will often be very noticeable.

          1 of 1 people found this helpful