7 Replies Latest reply on Nov 8, 2013 8:55 PM by Ramon Martinez

    How to get the average of an aggregated calculated field?

    Christopher Anthony Castro

      Is there a way that I could get the average of an aggregated calculated field? For example, I have to get the KPI value, hourly and daily. To get the hourly KPI value, I just need to have a calculated field that looks like this (SUM(numerator)/SUM(denominator) = hourlyKPI).

       

      For better understanding, see example below:

      DateNumeratorDenominatorHourlyKPI (numerator/denominator)
      September 2, 201373,652,761
      77,863,348
      94.59%
      September 3, 201393,991,656
      99,148,793
      94.80%
      September 4, 2013255,935,818
      270,673,049
      94.56%
      September 5, 2013254,837,665
      270,399,691
      94.24%
      September 6, 2013251,146,578
      267,802,542
      93.78%
      September 7, 2013269,358,841
      288,202,100
      93.46%
      September 8, 2013264,722,167
      281,225,469
      94.13%
      September 9, 2013249,764,652
      264,189,274
      94.54%

       

      Now the tricky part is this, to get the KPI value per day the requirement is to get the average of hourlyKPI. The problem is, hourlyKPI is already aggregated, there's no way for me to get its average.

       

      For better understanding, I need to compute the dailyKPI like this.

      AVG(94.59%, 94.80%, 94.56%, 94.24%, 93.78%, 93.46%, 94.13%, 94.54%)

       

      Does anybody know of a solution or at least a workaround? We are currently in the middle of a project. Thank you.