12 Replies Latest reply on Feb 17, 2017 1:19 AM by Kir Mir

    Find the Max Value of a Sum

    Patrick Calnan

      Hi,

      I am sure that this is a very simple problem but I want to find the max value of a sum of values. I have provided an example in Excel where I want to sum by company and time period, and then I want to find the max and min in each period and show in a table.

       

      It is very straightforward in Excel, I just cant grasp calculations in Tableau yet

       

      Any help with this problem would be appreciated.

       

      PC

        • 1. Re: Find the Max Value of a Sum
          carlos.sanzporro.0

          Use Level of Detail expressions, example: {INCLUDE : MAX([Your Measure])}

           

          http://onlinehelp.tableau.com/current/pro/online/windows/en-us/calculations_calculatedfields_lod_overview.html

          1 of 1 people found this helpful
          • 2. Re: Find the Max Value of a Sum
            Ben Neville

            You're very close. You don't necessarily need your sum volume calculation, as this can be included in your table calculation (Calculation 1). Right now, you have the 2, 0 values in your table calculation, which cause the lookup to only look at the 2 values preceeding it, which is why you won't truly get the max value in the ENTIRE table. To remedy this, just adjust your Calculation 1 to be like the following:
            WINDOW_MAX(SUM([Volume]))


            This calculates the MAX value across the entire window. The value in this case being SUM([Volume]), thus it finds the maximum sum. Now, if you want to compute at a level that is not the one you're displaying (Company and time), then you will need to use Level of Detail expressions.

            • 3. Re: Find the Max Value of a Sum
              Mark Fraser

              Hi Patrick

               

              Do you have v9? on higher?

              The reason I ask, is that functionality to help, was only introduced from v9, I'm talking about Level of Detail (LOD).

              PS. You can do it without v9, its just easier with

               

              Cheers

              Mark

              • 4. Re: Find the Max Value of a Sum
                Patrick Calnan

                Hi Mark,

                 

                I am still down at v8 unfortunately.

                • 5. Re: Find the Max Value of a Sum
                  Patrick Calnan

                  Hi Ben,

                   

                  That is helpful, I am on version 8.2 so I do not have the LOD parameter.

                   

                  PC

                  • 6. Re: Find the Max Value of a Sum
                    Mark Fraser

                    Hi Patrick

                     

                    Interesting problem, and helps explain why everyone went crazy when LOD was introduced

                     

                    There are ways, with Tableau there normally is...

                    This is a quick hack, I'm sure it can be done better... the trade-off is that you need to display the hour/time.

                     

                    Basically, I leverage the RANK function to order the values, as you want the MIN and MAX we sort them one way, take the first item, then...

                    reverse the sort, and again take the first item... clever, eh!

                     

                    Anyway, here are the formulas -

                    MIN = IF RANK(SUM([Volume]),'asc') = 1 THEN SUM([Volume]) ELSE NULL END

                    MAX = IF RANK(SUM([Volume]),'desc') = 1 THEN SUM([Volume]) ELSE NULL END

                    I worked on your workbook but haven't provided it, because i have v9.2.4 you wont be able to open mine, hence the screenshots.

                     

                    The workaround isn't perfect, but it gets you were you wanted to be, just.

                    If i can think of a better way (without v9), I'll let you know

                     

                    Hope that's all clear

                     

                    Cheers

                    Mark

                    2 of 2 people found this helpful
                    • 7. Re: Find the Max Value of a Sum
                      pooja.gandhi

                      Hi Patrick!

                       

                      You can maybe create 2 calcs and set compute using > Time in the table calcs setting. Although like Mark says, this is WAY too easier post version 9.0

                       

                      Min: window_min(sum([Volume]))

                      Max: window_max(sum([Volume]))

                       

                      Place Time on level of detail, company on rows and double click on the 2 calcs to bring them on the view.

                       

                      Then create a 3rd calc: LAST() = 0 and place it in filters so only the last value per company is visible in the view (because window_min and max will fill all rows with the same value for all time values there are). Click on the dropdown of the LAST() calc in the filter and make sure compute using is set to Time for this one as well.

                       

                      1 of 1 people found this helpful
                      • 8. Re: Find the Max Value of a Sum
                        Patrick Calnan

                        Hi Pooja,

                         

                        That is very helpful, but the figures are not coming out correctly for me. This is one of the reasons I hate table calculations as I find it difficult to trust them.

                         

                        Do you know what I could be doing wrong?

                         

                        • 9. Re: Find the Max Value of a Sum
                          pooja.gandhi

                          Hi Patrick!

                           

                          Yes, I am not a fan of table calcs either especially if I have to use them after being used to LODs. You have to make sure addressing and partitioning are set correctly to make sure everything works well. I am not sure where you are getting the values from? For Kia, 190 and 17 are not even values of the volumes?

                           

                           

                          What are you using in the formulas for window min and max? Did you make sure compute using is set to > Time?

                          1 of 1 people found this helpful
                          • 10. Re: Find the Max Value of a Sum
                            Patrick Calnan

                            Hi Pooja,

                             

                            Thanks for that. I will look at it again. I hope that by doing table calculations consistently I will final "get them". At least you understand them

                             

                            PC

                            • 11. Re: Find the Max Value of a Sum
                              Ben Neville

                              Hi Patrick - table calcs are an important concept - they will let you do a lot of analyses that you can't otherwise. Especially if you aren't enjoying the power of v9 just yet. I've included a workbook with fairly detailed descriptions of what is going on in this example. Hope this helps your understanding.

                              1 of 1 people found this helpful
                              • 12. Re: Find the Max Value of a Sum
                                Kir Mir

                                Hi, help on the example of Ben Neville
                                how to show not only the maximum value for each company but also hour in which there was the maximum value

                                KIA 8 350
                                VOLVO 4 166

                                Thanks