7 Replies Latest reply on Mar 21, 2013 12:18 PM by Ashley Howard

    Use aggregated data in MAX function

    Lisa Bogan

      Hi.  Am new to Tableau and need to use aggregated data in a MAX function.  For example, sales are summed for each day and the result plotted on a chart (or in a table).  I need to find the MAX of those daily points.  Any suggestions? Once I know how to do that, I can spotlight when each new MAX occurs.  Thanks for the help!

        • 1. Re: Use aggregated data in MAX function
          Joshua Milligan

          Lisa,

           

          Welcome to the forums!   It's great to have you!

           

          One of the easiest ways to do this would be to plot on a line chart for daily sales and then turn labels on for Min/Max to highlight where they occur (see image below).  The same could be done for a bar chart or other visualization.  There are, of course, other options, which might work better in certain circumstances.  I've attached a workbook that shows a couple of examples.  Please take a look and let me know if any of it seems to apply to your particular case or if you have any additional questions.

           

          Regards,

          Joshua

           

          Min and Max.png

          • 2. Re: Use aggregated data in MAX function
            Elizabeth Morrison

            Thanks for the suggestion, Joshua.  I will add it to my book of 'good things' for use at a later date.  Didn't realize that chart and tabular form would work differently for this kind of thing.  The client wants the data in tabular form only - no charts (it's really a replication of an Excel spreadsheet that updates itself every day with new data). So, I have to find a way of spotlighting the new MAX of that already aggregated (SUM) data in tabular form. To have the new MAX pop up highlighted in red or something is just what they're looking for.  In tabular display I didn't find the LABEL option, which makes sense.  Any suggestions?

            • 3. Re: Use aggregated data in MAX function
              Ashley Howard

              **Updated""

               

              Elizabeth - Its pretty easy to do using a new calculation that asks if the daily sum([Sales]) is equal to the max sum([Sales]) in the month and gives a True/False Answer that you can use to highlight one of the rows.


              I attached an example using December Superstore Sales and included the step by step instructions to recreate it below.

               

              1) To begin, on Rows put Day(Order Date).

              2) Note: you'll want to place Order Date on the Filter limiting the range of dates to the current month.   You can use an equation so you don't have to update the filter monthly.  The equation would go under Conditions>By Formula and the Order Date would need to be set to discrete.  The equation would be:


              month(today())=month([Order Date])

              AND

              year(today())=year([Order Date])

               

              3) Now Place Sum[Sales] on Text.

               

              4) Then create new calculation I called Max Sales:  

               

              Window_max(sum([Sales]))=sum([Sales])

               

              5) Place this new calculation on the color shelf. Then Right click on the Max Sales pill.  And select Edit Table Calculation.

               

              Under Compete using select Advance.  Using the arrows move move Day of Order Date to the right box. Click Ok.

              Set "At the Level" to Month of Order Date.  Click Ok.

               

              6) You can then change the colors.

              1 of 1 people found this helpful
              • 4. Re: Use aggregated data in MAX function
                Elizabeth Morrison

                This is really close, Ashley.  Thanks so much.  Now, I have two other measures that need to have the same WINDOW_MAX formulas created for them and the new highs spotlighted.  Will Tableau allow more than one aggregated formula on the COLORS shelf?  If so, how do I do it?  I cannot share the template with you but it's essentially this:

                 

                columns - each of the last 5 business days

                rows - each of the following summed over each of the last 5 days, displayed as daily totals for each day:

                     sales (SUM[Sales])

                     customers (SUM[Customers])

                     sales ratio (a formula that looks at how many sales per customer) - AGG((SUM[Sales])/SUM[Customers)) (or           something like that)

                 

                I need to spotlight the MAX of each of these three but seem to only be able to put one measure on the shelf at a time.  If I create the formulas using your example (so helpful!) will the client need to manually put one MAX calculation on the shelf at a time or is there a workaround?

                • 5. Re: Use aggregated data in MAX function
                  Ashley Howard

                  From what I've found there is a limitation on the use of the Measure Names pill.  I haven't found a way yet to use it in calculations.  Others feel free to pipe in if you've found a work around.

                   

                  As a result, Elizabeth/Lisa, I think that you have two options.  I've highlighted both in the attached.  The first is to use a parameter that changes the highlight based on a selection of sales, order quantity or sales ratio.  The second is to use multiple sheets (one for each current measure with its own highlight calculation) and splice them together on a dashboard to create "one" sheet.

                   

                  Hope this helps.

                  -Ashley

                   

                  Edited to add the file.  Oops. Message was edited by: Ashley Howard

                  • 6. Re: Use aggregated data in MAX function
                    Lisa Bogan

                    Thanks, Ashley.  You are really helpful.  Can't do option 2 as this is already one of 5 sheets assembled to dashboard to mimic Excel presentation.  Will try playing with parameters and get back to you.

                    • 7. Re: Use aggregated data in MAX function
                      Ashley Howard

                      Will keep thinking about this and let you know if I think of something. I'm convinced there is a work around, I just haven't come up with it yet.