5 Replies Latest reply: Dec 22, 2016 10:43 AM by John Chatlani RSS

    rangeavg or set analysis

    John Chatlani

      Hi -

      I have some data in this form:

       

      dateidfixtureresult
      Jan-1Alamp5
      Jan-1Alamp4
      Jan-1Btable3
      Mar-6Alamp9
      Mar-6Alamp7
      Mar-9Btable7
      Mar-9Btable6

       

      I am plotting on a line chart fixture latest averages of min values by date.  So, here on Jan-1 the MIN(A) is 4 and Mar-6 MIN(A) is 7. Since the latest two results across the lamp happen to correspond to 4 and 7, I would like to display the average 5.5 for this dimension.  It is average across the latest 4 samples in my data set, I've shown only a small portion of the data.  I do not have date as a dimension because I am showing this summary per fixture in a line chart.

       

      I tried to keep fixture as a dimension and then to use avg(aggr(min(result), date, id)) but avg iterates over the entire data set instead over the last 4 records.  In the table chart I played with the accumulation but with no success, probably because I don't have a date as a DIM. Same issue when I try to use rangeavg(below()). Can someone lend a hand, maybe some set analysis to restrict my average to the top 4 dates?

      Thank you.