Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

Thanks

3 Replies
Not applicable
Author

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.

=FirstSortedValue(valuefield,-datefield, 10)

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

whiteline
Master II
Master II

Hi.

You could use set analysis. There is plenty of examples in the community and QV help.

Sum({<datefield={"<$(=Max(datefield)-10)"}>} valuefield)

IAMDV
Master II
Master II

Hi,

I've made some video tutorials on using FirstSortedValue() function. I hope this will be useful.

http://qlikshare.com/392

Cheers,

DV