aggregate function on values based on last n most recent dates
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?
Re: aggregate function on values based on last n most recent dates
I have figured out how to get the 10nth most recent date (or value) using FirstSortedValue() but I am still stuck on how to apply the aggregate function since using the returned date (it is really a datetime) for a greater than "If" does not work as it may not be unique and the one value on its own does no good.
To make things even more complicated I realize that I may not even have 10 records. In this case I need to get the sum or average of what is available.
So I should restate my problem to be to get the average or sum of a value on the Past 10 *or less whichever is greater* transactions.
Script query is seeming more immenent - please tell me there is another way