4 Replies Latest reply: May 30, 2017 5:49 AM by Tim Poismans

# FirstSortedValue with Sum

Hi all.

For a project of a customer, I need to check a list of files to see when they were created and when the first payment linked to that file was made.

Some files however, have multiple payments on the first date.

Example:

 Filenr Date created Date Amount text1 GKS45618 8/09/2016 4/01/2017 0 random GKS45618 8/09/2016 6/01/2017 45 payment GKS45618 8/09/2016 6/01/2017 187 payment GKS45618 8/09/2016 12/01/2017 0 random GKS45618 8/09/2016 12/01/2017 249 payment GKS45618 8/09/2016 13/01/2017 0 random

As you can see, there are two payments made on 6/01/2017. So for the first payment for file GKS45618, it should say 232. But for the love of god, I can't figure it out.

So far, I've come up with this formula, which doesn't do the trick:

FirstSortedValue(Aggr(Sum({<text1={'payment'}>}Amount),date),date)

Anyone that could help me out or point out what I'm doing wrong?

• ###### Re: FirstSortedValue with Sum

Try this

=FirstSortedValue(Aggr(Sum({<text1={'payment'}>}Amount),Date),Aggr(Only({<text1={'payment'}>}Date), Date))

• ###### Re: FirstSortedValue with Sum

Hi

Sorry for the late response.

I tried the formula, but it only seems to work in some cases, though only when I modified it like this:

=FirstSortedValue(Aggr(Sum({<text1={'payment'}>}Amount),Date, Filenr),Aggr(Only({<text1={'payment'}>}Date), Date, Filenr))

[I added the Filenr within the Aggr.]

For some Filenr, it gives the correct amount, for some it gives double, for some it gives Null-value.

Any idea why it would do that? Seems strange that it does work for some, but not all.

Would it be an option to do a Sum and Group By in the script to solve the issue?

Kind regards,

Tim

P.S.: I don't have a licensed version of QlikView, so can't open .qvw files.

• ###### Re: FirstSortedValue with Sum

To add in, the formula (with Filenr) does seem to work, except I have to select 'payment' under text1.

When I select it, the formula does work, unselecting it however, makes a mess out of it.

• ###### Re: FirstSortedValue with Sum

Hi again.

Finally managed to solve it:

=FirstSortedValue({<text1 = {'payment'}>}Aggr(Sum({<text1={'payment'}>}Amount),date, Filenr),Aggr(Only({<text1={'payment'}>}date), date, Filenr))

seems to do the trick. I added set analysis in the FirstSortedValue to filter out the payments out of all the actions in the table. Pretty obvious.

Thank you stalwar1 for helping me get this far.