FirstSortedValue with Sum

[timpoismans]

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?

[sunny_talwar]

Try this

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

[timpoismans]

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]

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 · Accepted solution]

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.

[Jsobrinho77]

Hi guys,

I need to sum the values Qty considering the period, so my dataset is like this:

Product	Date	Qty	KEY	Sum	Aggr
1	01/12/2021	93	1|10|20|Text	0	0
1	01/11/2021	93	1|10|20|Text	0	0
1	01/08/2021	279	1|10|20|Text	465	279
2	01/09/2020	93	2|10|20|Text2	0	93

my sum measure is:

sum(aggr(Sum({$<[Date]={"$(='>=' & AddMonths(MonthStart(max([Date])),-11)& ' <= ' & Date(Max([Date]), 'DD/MM/YYYY'))"}>} Qty),[Key]))

my aggr measure is:

=sum(aggr(FirstSortedValue(Qty ,-aggr(max(Date),[Key])),[Key]))

This return null
=FirstSortedValue({<[Date]={"$(='>=' & AddMonths(MonthStart(max([Date])),-11)& ' <= ' & Date(Max([Date]), 'DD/MM/YYYY'))"}>}
Aggr(Sum({<[Date]={"$(='>=' & AddMonths(MonthStart(max([Date])),-11)& ' <= ' & Date(Max([Date]), 'DD/MM/YYYY'))"}>}Qty),[%SK_PriceCheck]),
Aggr(Only({<[Date]={"$(='>=' & AddMonths(MonthStart(max([Date])),-11)& ' <= ' & Date(Max([Date]), 'DD/MM/YYYY'))"}>}[Key]), [Key]))

The expected result is

Product	KEY	Sum
1	1|10|20|Text	465
2	2|10|20|Text2	93

Please, can someone help me?

thank you