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


      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





      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