4 Replies Latest reply on Feb 21, 2019 11:04 AM by Pedro Gallo

    Sum & Avg in Hierarchies

    Pedro Gallo

      Hi team,

      I have a problem calculating a value based on a dynamic time frame (depending on the date/time filter) & hierarchy created.

      The idea is to show the result based on the dimension of the hierarchy selected:

      Below is the following information.

       

      Hierarchy (already created):

      -Zone

      --Group Name

      ---Operation Name

       

      Variables:

      -Date/Time

      -Zone

      -Group Name

      -Operation Name

      -Part Per Events (constant)

      -Total Events: Good Events / Bad Events / No Events

      -Average Time (sec)= Avg(Time)

      -Time1 = Sum (Time1)

      -Time2 = Sume (Time2)

       

      Formula & conditions:

      For each Operation Name: Potential = [Total Events(only good events)*Parts Per Events] / ( { [  Avg(Time) * Total Events(only good events) ] + Time1 + Time2 } / 3600)

      For each Group Name: Potential = Sum (Potential's Operation Name)

      For each Zone: Potential = Avg (Potential's Group Name)

       

      Below is an example of what is expected :

       

           

      ZoneGroup NameOperation NameParts Per EventsTotal Events
      (Good Events)
      Average Time (sec)Time1 (sec)Time2 (sec)Potential
      XAA1119200.0324009.7
      XAA2218354.01654156713.5
      XAA3112288.0478011.0
      XAA418443.0178922303.8
      XAA5210546.0143012.9
      Total A50.9
      XBB1110443.044291054.0
      XBB2117567.056206.0
      XBB3316655.012520011.0
      XBB4115430.0511204.7
      XBB529641.0678550983.7
      Total B29.4
      XCC124445.01805091.5
      XCC2118698.0254274.3
      XCC3419445.0340566018.9
      XCC4113223.0418414.1
      XCC518121.0525204.6
      Total C43.4
      Total X Avg41.2

       

      Thanks for the support