2 Replies Latest reply on Jan 30, 2018 1:42 AM by adrien serout

    aggregate a already aggregated field or LOD or Window_xxx

    adrien serout

      Hi,

      First time, I am posting because I normally found my questions already answered but this time, even though, there are plenty of answers already on the same topic, I just don't manage to do it.

       

      I have a simple SQL, that computes a duration between 2 events for each filename. I used a calculated field "duration_color", If the duration for this filename is higher than the overall average, 1 otherwise 0

       

      if avg([duration_testing_in_hr])> WINDOW_AVG(avg([duration_testing_in_hr])) then 1

      ELSE

      0

      END

       

      Now, I want to calculate the number of rows where  we are above the average duration

      WINDOW_SUM([duration_color]) ....(=128)

       

      So far so good, but now, I want to do it in a single sheet without having to add all the filenames (so without using Window_SUM())

       

      average time

      {INCLUDE [identifier]: Avg ( [duration_testing_in_hr])}

       

      Number total of files/events

      {INCLUDE [identifier]: countd([identifier])}

       

      Number of files above average time

      ????? Obvisouly, it shows 0 because there is nothing in the window, so I know why but I don't know how to solve it

      I tried Sum(duration_color) but it is the aggreagte of an aggregated field.

      I tried LOD but I cannot use a calcualted field in a LOD... so ???

       

       

      PS: I will add a workbook but I first need to create a clean dataset

      Many thanks

      Adrien