2 Replies Latest reply on Mar 19, 2018 9:02 AM by Bob Lindas

    Average of Average per time Aggregate Specified

    Bob Lindas

      Hello all,

       

      I'm hoping this would be achievable within Tableau and that someone could help me to get started.

       

      I have a dataset that has multiple users, in multiple departments, who generate multiple samples throughout the course of a year.

       

      What I would like to achieve is somewhat complex, but I'll start out with a basic example. On a time graph, for a given time aggregate that a tableau user may choose (day, week,month, etc), what I would like to do is take all samples of a unique user for the time aggregate specified, and average all their samples into 1 sample. This would be done for each unique user (lets call this unique user average), and then in the end, all these unique user averages would be averaged into 1 final sample for that given time aggregate, per department.

       

      To put this into an example

       

      Department ADepartment B
      DeptA U1DeptA U2DeptB U1DeptB U2
      01-Jan535
      02-Jan5
      03-Jan3332
      04-Jan32
      05-Jan31
      06-Jan3313
      07-Jan121
      08-Jan222
      09-Jan151
      10-Jan32
      11-Jan3334
      12-Jan311
      13-Jan53
      14-Jan145

       

      If the tableau user chooses weekly time aggregate, what I'm trying to achieve is the following

       

      Dept A week 1 average = take samples from Jan 1 to 7, and avg(avg(DeptA U1)+ avg(DeptA U2)) = 3.1

      Dept B week 1 average = take samples from Jan 1 to 7, and avg(avg(DeptB U1)+ avg(DeptB U2)) = 2.3

       

      Dept A week 2 average = take samples from Jan 8 to 14, and avg(avg(DeptA U1)+ avg(DeptA U2)) = 2.8

      Dept B week 2 average = take samples from Jan 8 to 14, and avg(avg(DeptB U1)+ avg(DeptB U2)) = 2.7

       

      And then graphed over time

       

      Any help on this would be greatly appreciated.