3 Replies Latest reply: Oct 14, 2012 7:21 AM by Deepak Vadithala RSS

    aggregate function on values based on last n most recent dates

    Jouni Pieniniemi

      I have researched the range and the pick functions and can't quite seem to get this

       

      I have a requirement to produce averages and sums various values based on the "last 10" transactions, where we have a date to evaluate which transactions are the most recent. It's needed as chart expression.

       

      I have a transaction table in memory where I have the values to be summed/averaged, and each record also has the date.

       

      I may be missing something obvious, but it seems the functions available all perform the range lookup AND aggregate on the same field - in my case I want my date to be used to find the range and another field to be the subject of the aggregate.

       

      I should add that the transaction table is not sorted by this date.

       

      Can I do this without having to create in-memory tables based on a query that returns the a sorted result with a limit?

       

      Thanks