Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

timpoismans
Valued Contributor

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:

 

FilenrDate createdDateAmounttext1
GKS456188/09/20164/01/20170random
GKS456188/09/20166/01/201745payment
GKS456188/09/20166/01/2017187payment
GKS456188/09/201612/01/20170random
GKS456188/09/201612/01/2017249payment
GKS456188/09/201613/01/20170random

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?

Tags (2)
1 Solution

Accepted Solutions
timpoismans
Valued Contributor

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.

4 Replies

Re: FirstSortedValue with Sum

Try this

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

timpoismans
Valued Contributor

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.

timpoismans
Valued Contributor

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.

timpoismans
Valued Contributor

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.

Community Browser