8 Replies Latest reply: Sep 11, 2012 7:27 AM by whiteline _ RSS

    Filtering Data with max

      Hello

       

      I have following data :

       

      OffNum      OffLine     Version     Amount

           1               1               1               10

           1               2               1               15

           1               1               2               9

           1               2               2               14

           2               1               1               4

           2               2               1               5

       

       

      I would like to calculate the sum of the amount but only for the highest version. There is no limit in the numbers of OffNum, OffLine or Version. But I only need the highest version per offnum.

       

      Anybody any idea?

       

      Thanks

        • Re: Filtering Data with max
          whiteline _

          You can use:

          =Sum({$<Version={"=Version=Max(Version)"}>} Amount)

          or

          =Sum({$<Version={"=Version=Max(total Version)"}>} Amount)

          depending on what your dimensions are.

            • Re: Filtering Data with max

              Can't get it to work. I've added a example with your answer.

               

              Hope you can check it out.

                • Re: Filtering Data with max
                  whiteline _

                  I said that depending on your needs you may have to add total qualifier.

                  Use the second expression from the first post:

                  Sum ({$<offertes.Version={"=offertes.Version=Max(total offertes.Version)"}>} offertes.OffAmtDLc1 )

                    • Re: Filtering Data with max

                      Correct me if im wrong, but when using the total you wil only get the sum of the highest version.

                      The lines represent invoices lines.

                       

                      I need the sum of all the invoices there highest version. In my previous example i want the sum of all the bold lines.

                      Not only from those with version 2 because that is the highest version.

                       

                      I'm sorry if these was not clear, I have not explained this correctly in my first post.

                       

                      OffNum      OffLine     Version     Amount

                           1               1               1               10

                           1               2               1               15

                           1               1               2               9

                           1               2               2               14

                           2               1               1               4

                           2               2               1               5

                       

                       

                      Thank you for your help.

                        • Re: Filtering Data with max

                          I am not sure if this is what you want, but if you have OffNum as dimensions, this function will show the total of the highest version within a OffNum, so it shows:

                           

                          OffNum     Sum(Amount)

                               1                 23

                               2                  9

                           

                          If you select Expression Total in Total Mode for your expression you will get a total at the top, like this:

                           

                          OffNum     Sum(Amount)

                                                  32

                               1                 23

                               2                  9

                          • Re: Filtering Data with max
                            whiteline _

                            Ah, thats rather complex.

                             

                            Try this one:

                            =Sum(aggr(if(offertes.Version=Max(total<offertes.OffNum> offertes.Version), Sum (offertes.OffAmtDLc1)), offertes.OffNum, OfferteKey))