2 Replies Latest reply on Sep 26, 2018 5:55 AM by Tom Hull

    Getting STDEV of data with lowest aggregated average

    Tom Hull

      Hi,

       

      I have a pool of time data from athletes. This is comprised of the following fields: Date, trial number (multiple trials per day), split number (multiple splits per trial) and the actual time for each split.

       

      My target is to plot the mean of the trial from each date that has the lowest mean of the splits. I have achieved this using FIXED LOD calculation...

       

      {FIXED [Date], [Trial]: AVG([Time])}

       

      and plotted the MIN of this calculation vs Date in a view.

       

      However, I also want to plot the standard deviation of the splits from the trial that had the lowest mean split time. Following the same method as above but substituting the 'AVG' in the FIXED calculation for 'STDEV' I get the lowest STDEV from a given date but this is not necessarily from the same trial that had the lowest mean split.

       

      I have attempted using table calculations to get the relevant STDEV value from trial with the lowest mean but cannot seem to get this correct.

       

      I have included a packaged workbook and also a visual of what I am trying to achieve for extra clarity. PWB is 10.4 (32-bit) but need to deploy solution in 2018.1.

       

      Any help is most appreciated.

      Thanks,

      Tom

       

      Visual explanation:

      -I want to plot the mean and STDEV highlighted in the red circle, the lowest mean from the day and STDEV from that trial (although not the lowest STDEV on the day)

      -Currently using a fixed calculation described above the lowest mean works well

      -When doing this with STDEV though I get the lowest STDEV from the day (blue arrow), rather than the STDEV associated with the trial with the lowest mean (green arrow).

      -How can I get the value indicated in green displayed in the top pane.