14 Replies Latest reply on Feb 13, 2017 2:34 PM by Joshua Milligan

    LOD: Obtain Calc. field output based on multiple column criteria

    Robbert Korthals

      Hi Tableau Community,

       

      I have the following question and hope you guys can help me out

       

      I have the following data, and my aim is to populate the last 3 columns using the the "User" and "Order Classification":

       

      UserOrder ClassificationSeller1st Order2nd Order3rd Order
      Abel1st OrderBlokker
      Abel2nd OrderC&A
      Abel3rd OrderZara
      Greg1st OrderWE
      Greg2nd OrderPullman

       

      The output should look as follows:

       

      UserOrder ClassificationSeller1st Order2nd Order3rd Order
      Abel1st OrderBlokkerBlokkerC&AZara
      Abel2nd OrderC&ABlokkerC&AZara
      Abel3rd OrderZaraBlokkerC&AZara
      Greg1st OrderWEWEPullmanNULL
      Greg2nd OrderPullmanWEPullmanNULL

       

      It seems a lot like a LOD calculation, however I can't really get my head around how transpose the "seller" column over the multiple columns.

       

      Moreover, I would like to avoid doing custom SQL, and do it straight within tableau.

       

      Your help would be super appreciated!

       

      Robbert

      1st Order

        • 1. Re: LOD: Obtain Calc. field output based on multiple column criteria
          Norbert Maijoor

          Goedenavond Robbert,

           

          Find my approach based on LOD expression below as reference and stored in attached workbook version 9.3

           

          1st order: if [Order Classification]="1st order" then {include [Order Classification]: min([Seller])} END

           

          • 2. Re: LOD: Obtain Calc. field output based on multiple column criteria
            Joshua Milligan

            Norbert Maijoor & Robbert Korthals,

             

            I don't understand. I can get that table without any calculations, just arranging the fields:

             

             

            But there must either be something more complex either in the data or in the desired output. 

             

            Robbert, your desired output includes a row for every order and a column for every order classification.  But why?  What's the benefit of that over a simpler table?

             

            Best Regards,

            Joshua

            • 4. Re: LOD: Obtain Calc. field output based on multiple column criteria
              Robbert Korthals

              Hallo Norbert Maijoor & Joshua Milligan

               

              It is probably because I am new to the community (read: Tableau Noob), but where can I find the attached workbook 9.3?  So far, I am only getting a Count as output, and not the label of the store...

               

              FYI: My aim, when I have the above table format, is to label Abel and Greg either as "Loyal" or "Switcher" based on their first 3 purchasing destinations.

               

              For example: IF ([1st Order] = [2nd Order] = [3rd Order]) THEN "Loyal" ELSE "Switcher")

               

              Again, very much appreciated!

              • 5. Re: LOD: Obtain Calc. field output based on multiple column criteria
                Joshua Milligan

                Robbert,

                 

                Your calculation is very close.  Try something like:

                 

                IF ([1st Order] = [2nd Order] AND [2nd Order] = [3rd Order]) THEN "Loyal" ELSE "Switcher" END

                 

                You can attach the workbook using the link in the reply dialog

                 

                Just make sure to attach it as a .twbx (packaged workbook), otherwise it won't include the data and no one else will be able to open it.

                 

                Hope that helps!

                Joshua

                • 6. Re: LOD: Obtain Calc. field output based on multiple column criteria
                  Robbert Korthals

                  Haha... a little too quick with the formula, but you are right

                   

                  But just to check, Norbert Maijoor's example workbook is missing right?

                   

                  I am still quite curious why I can't make the following formula work

                   

                  >> 1st order: if [Order Classification]="1st order" then {include [Order Classification]: min([Seller])} END

                  • 7. Re: LOD: Obtain Calc. field output based on multiple column criteria
                    Norbert Maijoor

                    Goedenavond Robbert,

                     

                    Please check the original. The attachment is there

                     

                    • 8. Re: LOD: Obtain Calc. field output based on multiple column criteria
                      Robbert Korthals

                      Hi Norbert Maijoor and Joshua Milligan,

                       

                      I could not find the document because of the Internet Explorer browser... Now with Chrome I managed to locate the packaged workbook,

                       

                      But indeed, as Joshua Milligan mentioned, the formula is not quite what I am looking for as I still would like to have all rows filled as well (in contrast to the image below)

                       

                       

                      Any ideas?

                       

                      Robbert

                      • 9. Re: LOD: Obtain Calc. field output based on multiple column criteria
                        Joshua Milligan

                        Robbert,

                         

                        You can definitely do that.  It's not as complex as writing a calculation for each Order Status.  Here's the approach I took (sorry, I don't have 9.3 installed -- 9.1, 10.0, 10.1, and 10.2 -- but not 9.3!)

                         

                         

                        As you can see, you can get the rows and columns you want just with the placement of the fields.  Too fill in the NULL values, just use a calculation that looks up the WINDOW_MAX.  Specifically, the code is:

                         

                        WINDOW_MAX(MAX(Seller))

                         

                        placed on text and then from the drop down menu on that field select Compute Using > Seller.  That will make sure to find the single non-null value within the Order Classification.  You won't see any values when there hasn't been an order (e.g. the 3rd Order from Greg)

                         

                        For loyalty, consider a calculation like:

                         

                        IF {FIXED [User] : COUNTD([Seller])} = 1

                        THEN "Loyal"

                        ELSE "Not Loyal"

                        END

                         

                        That will determine how many distinct sellers a User has: (1 = Loyal)

                        It's a FIXED level of detail at the User level, so make sure you understand filter/context filter ramifications.

                         

                         

                        That can be used easily in the view, something like this:

                         

                         

                        This approach has a couple of overall benefits versus writing a lot of calculations and avoiding a calculation to compare each order status with others:

                        • Simplicity
                        • Flexibility (you're not limited to 3 Order Statuses -- have as many or few as you'd like.

                         

                        Hope that helps!

                        Joshua

                        • 10. Re: LOD: Obtain Calc. field output based on multiple column criteria
                          Joshua Milligan

                          Also upon reflection: If the first three order statuses are what's important, then just filter to those and add to context (the FIXED calculation will be done in that context).

                          • 11. Re: LOD: Obtain Calc. field output based on multiple column criteria
                            Robbert Korthals

                            Hmmm... Still not quite what I am looking for... This is because of the following:

                             

                            1. I am looking for a way to "hard-code" the values over all the rows (eg 1st order) using a calculated field in contrast to what is shown in the table output (ie as you intend to do with your window_max table calculation). The "hard-code" is necessary because later on, I would like to apply the "loyalty-segment" as a split in an area chart on a different worksheet. (Hence, my question is simply an interim step towards my final goal = area chart)

                            2. It is actually exactly my intention not to have flexibility with the loyalty formula (ie I want to be very explicit which columns to include in the formula -> eg. the 1st order and 2nd order, which is the reason that I am working towards the formula IF ([1st Order] = [2nd Order] = [3rd Order]) THEN "Loyal" ELSE "Switcher")

                             

                            Again, Joshua Milligan and Norbert Maijoor very much appreciated!

                            • 12. Re: LOD: Obtain Calc. field output based on multiple column criteria
                              Joshua Milligan

                              Robbert,

                              You mentioned:

                               

                              ... because later on, I would like to apply the "loyalty-segment" as a split in an area chart on a different worksheet. (Hence, my question is simply an interim step towards my final goal = area chart)

                               

                              Then, I think we're approaching this a bit backwards possibly.  It would be very helpful to understand your end goal much more completely, because it is very likely that there are multiple approaches to get there and what you've asked for might or might not be a necessary intermediate step.

                               

                              Best Regards,

                              Joshua

                              • 13. Re: LOD: Obtain Calc. field output based on multiple column criteria
                                Robbert Korthals

                                Hi Joshua Milligan,

                                 

                                Let me explain where I am going with this, yet it should be noted from the start that I will step away from my simplified example sketched above, yet fundamentally we are talking about the same challenge:

                                 

                                Basically, this is the table that I am currently looking at. The DeviceIDs are inherently Abel and Greg in our example above

                                 

                                As you can see, I have been able to segment orders as "1st succesful order", "2nd succesful order" and "3rd succesful order", by conducting LOD calculations using a combination of the columns "CreationTime" and "Status - RealOrder Correction" --> basically I have ranked/segmented the orders based on the CreationTime with the condition that the status is "DealerDelivered". So far, so good

                                 

                                Now I intend to create 3 columns in total, named "1st Succesful Order", "2nd Succesful Order" and "3rd Succesful Order" and allocate the "Seller" based on the field value in the column "Order Segmentation". Hence, the top DeviceID should get the seller "Maria" in all the rows for the column "1st Succesful Order", whereas the bottom DeviceID should get the seller "Bob" in all the rows, for the same column (ie "1st Succesful order"). The same logic would then apply to the column "2nd Succesful order" and "3rd Succesful order".

                                 

                                As a result, I will then be able to use the three columns as attributes in a tooltip for a gantt chart that I am creating. It is therefore that the values must be "hard-coded" in the data source.

                                 

                                I hope this helps!

                                 

                                Robbert

                                • 14. Re: LOD: Obtain Calc. field output based on multiple column criteria
                                  Joshua Milligan

                                  Robbert,

                                   

                                  I'm with you up to the "So far, so good".  But I'm afraid I'm still not following why you need three additional columns that are "hard-coded" in the data source to get the right tool-tips on a Gantt chart (there may well be great reason, I'm just not following you to that point yet).  Would you be able to take the sample data you originally provided:

                                   

                                  User

                                  Order Classification

                                  Seller

                                  1st Order

                                  2nd Order

                                  3rd Order

                                  Abel1st OrderBlokkerBlokkerC&AZara
                                  Abel2nd OrderC&ABlokkerC&AZara
                                  Abel3rd OrderZaraBlokkerC&AZara
                                  Greg1st OrderWEWEPullmanNULL
                                  Greg2nd OrderPullmanWEPullmanNULL

                                   

                                  (with some sample dates) and show me the Gantt chart you would create with the placement of the fields?  That may help me understand why you need the additional three columns.

                                   

                                  Best Regards,

                                  Joshua