topic Re: aggregate function on values based on last n most recent dates in QlikView

aggregate function on values based on last n most recent dates

Sat, 13 Oct 2012 00:26:54 GMT

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

Re: aggregate function on values based on last n most recent dates

Sat, 13 Oct 2012 01:10:19 GMT

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

Re: aggregate function on values based on last n most recent dates

whiteline — Sat, 13 Oct 2012 18:57:23 GMT

Hi.

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

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

Re: aggregate function on values based on last n most recent dates

IAMDV — Sun, 14 Oct 2012 11:21:34 GMT

Hi,

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

http://qlikshare.com/392

Cheers,