4 Replies Latest reply on Aug 16, 2016 1:31 AM by Anna Yureva

    Average over period of daily averages

    Anna Yureva

      Hi!

       

       

      I am trying to calculate AVG of daily averages of length of jobruns.

      The data is

      Start_dt
      ElapsedRunSecs
      01/06/20162
      01/06/20162
      01/06/20168
      02/06/201610
      02/06/20166
      02/06/20162
      03/06/20168
      03/06/20165
      03/06/20162
      04/06/20168
      04/06/20166

       

      Adding to the difficulty, this has to be put in a Gantt chart with 24-hour axis, and the jobs start at different hours (in the data, field Runstarttimestamp, it seems to matter).

       

      What I want to get is an Average of daily averages:  

      (   (2+2+8)/3  +  (10+6+2)/3 + (8+5+2)/3 +(8+6)/2 ) /4 = 5,5

       

      I have tried fiddling with the CFs in the gantt chart:

      WINDOW_AVG(AVG([Elapsedrunsecs])) = 5.8 (not right)

       

      {fixed [Start Dt]: avg([Elapsedrunsecs])}  gives different values for each of the Hourly periods: 5;  7  . Both not right.

       

       

      I tried also Running average but it also did not make sense to me and did not produce the result:

      RUNNING_AVG(sum([Elapsedrunsecs])/COUNTD([Start Dt])) .

       

      Another thing that confuses me here is that the CFs in the tooltip change their value depending on the hour when the job was run. What is needed, is unchanged value on the job level, regardless of the hour when the job was run. Is it even possible?

       

       

      Help will be much appreciated!

        • 1. Re: Average over period of daily averages
          Michel Caissie

          Anna,

           

          You can get Avg you are looking for using only LOD, but you have to do a little gymnastic of inclusion and exclusion to take into account the missing level of detail in the view, and the extra Hours level of detail that is

          changing the expected 5.5 or those  5 and 7.

           

          if you look on sheet 8,  your LOD calc gives the correct values for each StartDt.

          Now if you go at sheet 9, Start Dt have been removed from the view and to obtain the 5.5 avg you need to include the Start Dt in the calc.

           

          avg({include [Start Dt]:

                  avg({fixed [Start Dt]: avg([Elapsedrunsecs])})

                })

           

          If we continue on sheet 10 , we now add the Hours for X axis lod  and we get the values that you are stuck with.

           

          Moving to sheet 11 we get back the correct values by  excluding the Hours for X axis in the calc

           

          {exclude [Hours for X axis]:

              avg({include [Start Dt]:

                  avg({fixed [Start Dt]: avg([Elapsedrunsecs])})

                })

          }

           

          which is the final calc that you need in the final Gant Chart(Sheet 7).

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Average over period of daily averages
            Anna Yureva

            Michel,

             

            This worked! Thanks for explaining it step by step, it certainly makes a lot of sense!

             

            What about when there are multiple jobs ? When adding more jobs with similar data and filtering to only see results of one, the AVG result is correct (same 5.5) if the filter is added to context. However, when there is no need to filter out a single job, the values should be calculated only per job, and not for all values of that day. I will try to do a table calc on Jobname and see how it works.

            Do you think this is a correct approach here?

             

            Attaching a twbx with couple more jobnames.

             

            Regards,

            Anna

            • 3. Re: Average over period of daily averages
              Michel Caissie

              All you need is to add  jobname  in the  fixed  part of the calculation

               

              {exclude [Hours for X axis]:

                  avg({include [Start Dt]:

                      avg({fixed [Jobname],[Start Dt]: avg([Elapsedrunsecs])})

                    })

              }

              1 of 1 people found this helpful
              • 4. Re: Average over period of daily averages
                Anna Yureva

                This is perfect, Michel, thanks for your tremendous help!