7 Replies Latest reply: May 16, 2018 4:04 PM by Ben Petosa RSS

    Aggregation with FirstSortedValue

    Ben Petosa

      Hello Everyone,

       

      Here is an example of my data set (it is much larger):

       

      estimate_number     est_rev_nbr     description     amount

      307                                   0               Hotel               500

      307                                   1               Hotel               1000

      307                                   2               Hotel               1500

      307                                   0               Flight               200

      307                                   1               Flight               300

      307                                   2               Flight               400

      307                                   3               Flight               500


      The max est_rev_nbr is different depending on the description. I am looking to get Hotel($1500) and Flight($500), and there are many more components not shown here. I would also like this in a Pivot Table visualization as a measure, so I was using "sum" to just give me the value. I was thinking something like:


      sum(FirstSortedValue(amount, -est_rev_nbr)


      I appreciate any help with this.


      Thanks,


      Ben

        • Re: Aggregation with FirstSortedValue
          Sunny Talwar

          May be this

           

          Sum(Aggr(FirstSortedValue(amount, -est_rev_nbr), description))

          • Re: Aggregation with FirstSortedValue
            Ben Petosa

            Sunny,

             

            I know it has been some time, and not sure if you get a notification with this reply, but figure it is worth a shot. Your formula above works great for the main use case, which is a KPI queried by an internal job code. It doesn't seem to work in a table visualization though, and there is no query (shows all of the internal jobs in list form). I have tried a bunch of other things, and can't get it to work. Is there a different formula that would work for a table?

             

            P.S. For the data above, there is a job_code (say 9000), that would be at the beginning of each row.

             

            Thanks in advance, and I will try the community if I don't hear back from you.

             

            Ben

              • Re: Aggregation with FirstSortedValue
                Sunny Talwar

                Would you be able to share more information? like what is your dimensions? May be share a sample if possible

                  • Re: Aggregation with FirstSortedValue
                    Ben Petosa

                    All of the fields above/below are dimensions. I believe the issue is that the est_rev_nbr can be different for every estimate number. If we redo a budget, the est_rev_nbr will go up by 1. Some jobs have one estimate, and some have 4 or 5. Here is a better data sample (I am not sure how qvf's work, but can attach an excel spreadsheet if needed):


                    job_number     estimate_number     est_rev_nbr     description          amount

                       1000                          307                             0              Hotel               500

                       1000                          307                             1              Hotel            1000

                       1000                          307                             2              Hotel            1500

                       1000                          307                             0              Flight               200

                       1000                         307                              1             Flight            300

                       2000                          311                             0               Food               750

                       2000                          311                             1               Food            1100

                       2000                          311                             0               Flight              400

                      2000                          311                              1               Flight              300

                       3000                         411                              0            Flight             900

                       3000                          411                             0               Food              1750

                     

                    There are 3 different jobs here. My desired output is (not in this format):

                     

                    1000  Hotel: $1500

                    2000  Food: $1100

                              Flight: $300

                              Total(sum): $1400

                    3000  Flight: $900

                              Food: $1750

                              Total(sum): $2650

                     

                    You will notice that the max(est_rev_nbr) is different, depending on the estimate/job_number. I only need the description amount from the max(est_rev_nbr), so you will see that the "Flight" actually drops off from job_number 1000 since Flight doesn't have an est_rev_nbr of 2. I believe the formula you gave me before, takes the highest est_rev_nbr across the whole data set, and only uses those (which is how I did originally ask the question). I am thinking I need one more aggr or comma in your original equation, so it calculates the firstsortedvalue (or lastsortedvalue in this case) for each estimate_number. I hope this all makes sense.


                    P.S. Someone else offered this for a possible fix, but it only works in certain cases also. In this example,


                    Quote_number = est_rev_nbr

                    Estimate = amount


                    = Sum({<Quote_number = {"= Max(Quote_number)"} >} Estimate)

                    OR

                     

                    = Sum(Aggr(Sum({<Quote_number = {"= Max(Quote_number)"} >} Estimate), Description))


                    Thanks, as always, for your help,


                    Ben