9 Replies Latest reply on Aug 28, 2018 12:51 PM by Michael Gillespie

    Explain MAX - Need someone to dumb-down the vernacular

    Stephen Groff

      The word MAX seems pretty straight forward to me...

       

      In relation to Tableau if I say something like this

       

      {FIXED [Serial #]:MAX([Shipping Cost])}

       

      I assume that Tableau is going to spit out the highest overall shipping cost at the ROW level per Serial #.

       

      Which would mean that if I have several rows with the same Serial # and each row has a different shipping cost; Tableau will spit out the row with the highest value (shipping cost).

       

      Now, I don't know if that is correct, but that is my assumption.

       

      HOWEVER, I run into the brain buster when I see calculations as such:

       

      {FIXED [Date], [Serial #]:MAX(IF [GROUP]='Dealership' THEN "True" ELSE "False")}

       

      What in the heck is the MAX function doing here?  I can't even attempt to assume and when Tableau says...

      "Returns the maximum of a single expression across all records or the maximum of two expression for each record"...  That's a bunch of hogwash vernacular...

       

      I cannot wrap my head around what the MAX function would do for String values.

        • 1. Re: Explain MAX - Need a someone to dumb-down the vernacular
          Deepak Rai

          Hi Stephen,

          For This:

           

           

          YOU R CORRECT in case of above

           

           

          This has to Return Maxi,um Value if Group =Dealership for Each Date and Serial #.

           

          So if you have many dates and each date has different Serial# then for each date and Serial #, if Group is dealership you will get TRUE using MAX, as TRUE >FALSE, so MAX Works.

           

          What i will do, I wont write TRUE False. I will Write 1 and 0 like this, Because 1>0 so there is no Confusion and it will always be True. My formula will be

           

          {FIXED [Date], [Serial #]:MAX(IF [GROUP]='Dealership' THEN 1 ELSE 0 END)}

           

          So if Group =Dealership, I will get 1 across that row for that date and Serial Number and for same date and serial number, I will get 0, if Group<>Dealership.

           

           

          Thanks

          Deepak

          1 of 1 people found this helpful
          • 2. Re: Explain MAX - Need a someone to dumb-down the vernacular
            Jim Dehner

            Hi Stephen

             

            Interesting question - because your question extends beyond MAX and into LOD expressions

            First lets talk MAX - and MAX comes in 2 forms - first is determining the MAX value and the second is a function that compares 2 arguments and returns the larger (i will put that on the shelf and only focus on the first

             

             

            MAX in the first context is going to return the maximum value as determined by the rest of the calculation (and MAX can include numbers, dates or alphanumeric values depending on the type of data)

            In your first example {FIXED [Serial #]:MAX([Shipping Cost])}  

            the expression in words says for each serial number look over all the shipping costs and return the maximum value --- do not confuse that with "Row" level -- an LOD is creating a new level of aggregation in your data - each LOD is creating a level above the level of the data in the expression (in this case shipping cost (the granular level)

            You can use and LOD as an argument in another LOD and go to one higher level of aggregation and so on - each level is 1 more step away from the granularity of the raw data

             

            your second expression    {FIXED [Date], [Serial #]:MAX(IF [GROUP]='Dealership' THEN "True" ELSE "False")}

             

            is looking at all the combinations of Date and Serial number --- and for each of those combinations it looks at a Group and if it is Dealership - the expression returns a True - if not a false --- the MAX is going to take the MAX alphabetically of those either True or False -----    for example - lets say you had on a single date and singel serial number (one combination as defined by the LOD)  you have 10 records with various "Group" values - one record is Dealership all the rest are not - so you have 1 True value and 9 False values  --- max is going to pass the True (max alphabetically) as the result of the LOD to the combination of DAte/Serial --- (i.e. there was a record with Group =Dealership

             

             

            You can also  use Max with dates as in  {Fixed : max(date)}  returns the latest date in the data set  which can be used in expressions to find the value of a measure on the last date in the data set   if [Date] =  {Fixed : max(date)} then Sales end

             

             

            Hope that helps

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: Explain MAX - Need a someone to dumb-down the vernacular
              Stephen Groff

              Okay... let me see if I grasp this...

               

              So anytime 'Dealership' shows up, it's going to count 1 using the MAX function.

               

              What's the point of saying {FIXED [Date],[Serial #]:  or {FIXED [Group],[Serial #]: or {FIXED [Date]:......   if it's going to assign a 1 to everything that says "Dealership"?

               

              Wouldn't just be easier to do this kind of a calculation:

               

              {FIXED [Date],[Serial #]:COUNTD(IF [Group]='Dealership' THEN [Serial #] END)}

               

               

              1 of 1 people found this helpful
              • 4. Re: Explain MAX - Need a someone to dumb-down the vernacular
                Stephen Groff

                Thanks for the Reply Jim... let me finish reading and I'll respond.  Appreciate your time.

                1 of 1 people found this helpful
                • 5. Re: Explain MAX - Need a someone to dumb-down the vernacular
                  Stephen Groff

                  Thank you for the in-depth reply Jim, I do appreciate your time.  If you couldn't already tell, I am a novice.  I've used Excel for years and Tableau only since June, but Tableau isn't Excel and I'm having a hard time rearranging my thought processes.  So many things I can do in easily in Excel seem impossible with my current level of knowledge within Tableau.  My boss expects miracles out of me, however, diving right into LODs is a hard place to start.  All this granularity and level of detail talk is incredibly foreign.

                   

                  Your explanation has been printed out and now resides on my wall.  Thank you.

                   

                  One question I have... that isn't real obvious to me...

                   

                  I was told that creating a {FIXED } LOD is like pulling a separate query out of your data and adding it to the view.  Whether that's true or not, I don't know, but I'm not too keen on the part highlighted below:

                  {FIXED [Date],[Serial #]

                   

                  What exactly am I saying here?  FIX/FREEZE the Data to Date and Serial number based on my MAX(GROUP='Dealership")... what does the date and serial # have to do with the group I'm looking for?  Why not just write out a calculation that says "IF [GROUP] = Dealership then 1?

                  1 of 1 people found this helpful
                  • 6. Re: Explain MAX - Need a someone to dumb-down the vernacular
                    Deepak Rai

                    {FIXED [Date],[Serial #]:COUNTD(IF [Group]='Dealership' THEN [Serial #] END)}

                     

                    Yes This will be Good, It would COUNT Distinct Serial Numbers For Particular Date and Serail# For Which Group is Dealership.

                     

                    Suppose you have This Data

                     

                     

                    DateSerialGroup
                    1/1/20181Dealer
                    2/1/20182Dealer
                    1/1/20183Finance
                    2/1/20184Supply
                    1/1/20185Dealer
                    2/1/20186Finance

                     

                    Then

                     

                     

                    and if you want to see per date

                     

                    Thnaks

                    Deepak

                    1 of 1 people found this helpful
                    • 7. Re: Explain MAX - Need a someone to dumb-down the vernacular
                      Stephen Groff

                      Deepak,

                       

                      OHHHHHHHHHHH!

                       

                      The light bulb just came on.  Jeez, no wonder I had to take College Algebra twice.

                       

                      {FIXED [Date],[Serial #] - I'm looking for 'Dealership' by DATE, and then BY SERIAL #, it's specific to both the Date and the Serial #

                       

                      {FIXED [Date] - I'm looking for 'Dealership' by DATE only, it's only specific to DATE..

                       

                      Excellent Reply, thank you for giving me the visual representation of the problem and solution.

                      1 of 1 people found this helpful
                      • 8. Re: Explain MAX - Need a someone to dumb-down the vernacular
                        Deepak Rai

                        ..Please Close the Thread, so that your  Light Bulb also enlightens others.

                        Thanks

                        Deepak

                        1 of 1 people found this helpful
                        • 9. Re: Explain MAX - Need someone to dumb-down the vernacular
                          Michael Gillespie

                          LOD's are fun, but not obvious.  Here's how I usually explain it.

                           

                          First, a basic concept in Tableau: The View.  That term refers to the 'canvas' of the worksheet - the place where your viz shows up when you put things on the Row and Column shelves.  EVERYTHING that Tableau does is in reference to the data you have placed in the view.  So if you SUM() a bunch of invoice amounts, and you have Customer Name on the ROW shelf, you'll sum at the Customer Name level; if you put Product Name on ROWs, then the invoice sums are at the Product Name level.  Make sense so far?

                           

                          What an LOD allows you to do is tell Tableau to do stuff differently from what's in the view.  That's it.

                           

                          On to the specifics, then!


                          There are 3 kinds of LOD expressions, and they tell Tableau exactly how to calculate things in different ways.

                           

                          FIXED says: No matter what may or may not be in the view, always calculate this expression at the following level(s) of detail.  So to take your question above, do your calculation at the combination of DATE and SERIAL # fields, no matter what may be in the view already.

                          INCLUDE says: EVEN IF the following field or fields are not in the view, do your calculation at that level of detail.

                          EXCLUDE says the opposite: EVEN IF the field(s) ARE in the view, IGNORE THEM when you do the calculation.

                           

                          There's a lot more to it, but that's the conceptual framework I use to remind myself what's going on.  Is that helpful?

                          1 of 1 people found this helpful