Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Try this
=FirstSortedValue(Aggr(Sum({<text1={'payment'}>}Amount),Date),Aggr(Only({<text1={'payment'}>}Date), Date))
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.
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.
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.
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