Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
timpoismans
Specialist
Specialist

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?

1 Solution

Accepted Solutions
timpoismans
Specialist
Specialist
Author

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.

View solution in original post

5 Replies
sunny_talwar

Try this

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

timpoismans
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Creator
Creator

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