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

    FirstSortedValue with Sum

    Tim Poismans

      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?

        • Re: FirstSortedValue with Sum
          Sunny Talwar

          Try this

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

            • Re: FirstSortedValue with Sum
              Tim Poismans

              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
                  Tim Poismans

                  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
                    Tim Poismans

                    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.