14 Replies Latest reply on Mar 9, 2016 10:16 AM by cignior

    Tableau Calculated Field - simpler way?

    cignior

      I'm not a programmer by education... I'm more of a hobbyist. Is there a simpler way to code this in Tableau? There are way more iterations of my code than pasted into this post. But you get the idea:

       

      IF [Product] = "PRODUCTA" AND [% Attainment] < 0.5 THEN 0 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "60-20-20"    AND [% Attainment] = 0.5 THEN 88 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "60-20-20"    AND [% Attainment] = 0.5  THEN 88 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "60-20-20"    AND [% Attainment] = 0.51  THEN 176 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "60-20-20"    AND [% Attainment] = 0.52  THEN 264 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.5  THEN 66 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.51  THEN 132 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.52  THEN 198 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.53  THEN 264 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.54  THEN 330 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.55  THEN 396 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.56  THEN 462 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.57  THEN 528 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.58  THEN 594 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.59  THEN 660 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "45-40-15"    AND [% Attainment] = 0.6  THEN 726 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.5  THEN 110 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.51  THEN 220 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.52  THEN 330 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.53  THEN 440 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.54  THEN 550 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.55  THEN 660 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.56  THEN 770 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.57  THEN 880 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.58  THEN 990 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.59  THEN 1100 ELSEIF [Product] = "PRODUCTA" AND [H2 - Potential Tier Assignment Update] = "50-25-25"    AND [% Attainment] = 0.6  THEN 1210 END

       

       

      UPDATE: Sorry. I added a picture to those that would like to look at prettier code. I didn't realize that pasting my code would create a jumbled mess (I even inserted it as "Raw HTML") But I'll leave it there for anyone who wants to mess with my code.

        • 1. Re: Tableau Calculated Field - simpler way?
          Derrick Austin

          Hey Cignior,

           

          One way you could simplify this is nested IF statements, so you don't have to repeat the Product if statement/H2 if statements as much.

           

          For example,

           

          IF [Product] = 'PRODUCTA'

               THEN

                    IF [H2 - Potential Tier Assignment Update] = '60-20-20'

                         THEN

                              IF [% Attainment] = .5 THEN 88

                              ...

           

          Hope this helps!

          - Derrick

          • 2. Re: Tableau Calculated Field - simpler way?
            cignior

            So would the code look like this with multiple [H2 - Potential Tier Assignment Update]?

             

            IF [Product] = 'PRODUCTA'

                 THEN

                      IF [H2 - Potential Tier Assignment Update] = '60-20-20'

                           THEN

                                IF [% Attainment] = .5 THEN 88

                                IF [% Attainment] = .5 THEN 95

                                IF [% Attainment] = .5 THEN 105

                      IF [H2 - Potential Tier Assignment Update] = '50-25-25'

                           THEN

                                IF [% Attainment] = .5 THEN 88

                                IF [% Attainment] = .5 THEN 95

                                IF [% Attainment] = .5 THEN 105

                      IF [H2 - Potential Tier Assignment Update] = '45-40-15'

                           THEN

                                IF [% Attainment] = .5 THEN 88

                                IF [% Attainment] = .5 THEN 95

                                IF [% Attainment] = .5 THEN 105

             

            END

            • 3. Re: Tableau Calculated Field - simpler way?
              Nicholas Hara

              Hi Cignior,

               

              Is there a Formula for receiving the result?

               

              Something like: [%Attainment] * LEFT([H2 - Potential Tier Assignment Update],2) //this takes the first number from potential tier assignment.  Or in mathematical terms: Y=A*P From the look of the calculation, it appears that the equation would use an exponent except where [%Attainment] <.5

               

              I bet that you could make the calculation much simpler if there is a set rule.

              • 4. Re: Tableau Calculated Field - simpler way?
                cignior

                Nope. The H2 Potential Tier assignment numbers mean nothing. They are really just a classification of territories. No need to separate the 50-25-25 and perform math.

                • 5. Re: Tableau Calculated Field - simpler way?
                  Derrick Austin

                  Yes, that is right! Except cleaned up a bit (ELSEIFs and END's were missing)

                   

                  IF [Product] = 'PRODUCTA'

                       THEN

                            IF [H2 - Potential Tier Assignment Update] = '60-20-20'

                                 THEN

                                      IF [% Attainment] = .5 THEN 88

                                      ELSEIF [% Attainment] = .5 THEN 95

                                      ELSEIF [% Attainment] = .5 THEN 105 END

                            ELSEIF [H2 - Potential Tier Assignment Update] = '50-25-25'

                                 THEN

                                      IF [% Attainment] = .5 THEN 88

                                      ELSEIF [% Attainment] = .5 THEN 95

                                      ELSEIF [% Attainment] = .5 THEN 105 END

                            ELSEIF [H2 - Potential Tier Assignment Update] = '45-40-15'

                                 THEN

                                      IF [% Attainment] = .5 THEN 88

                                      ELSEIF [% Attainment] = .5 THEN 95

                                      ELSEIF [% Attainment] = .5 THEN 105 END

                            END

                  END

                  • 6. Re: Tableau Calculated Field - simpler way?
                    Nicholas Hara

                    Thanks for the clarification.

                     

                    I guess the core question I have is, how are the final values in the THEN statement related to the values in the IF statement?

                     

                    What is the rule for determining the value?

                     

                    Is it arbitrary or do the numbers have a meaning?

                     

                    Do you have a sample workbook that we can look at?

                    • 7. Re: Tableau Calculated Field - simpler way?
                      Kent Sloan

                      Hi Cignior,

                       

                      The format mentioned by Derrick would certainly be a great recommendation for cleaning up the structure of the syntax. That being said when facing a calculation this large I would really look for a way to move it into your data source to avoid slow performance in the workbook.

                       

                      If this is not possible because the % Attainment calculation needs to be dynamic I think you might be able to simplify the calculation by building some of the inputs into the data set. Looking at your syntax it appears that for each product there is a preset range of values that determines which code is assigned to the product. If you could build out a product level table and join it onto your data set you might be able to write a single calculation that covers all of your products. So a calculation using the table below would check if the score matched value 1 if so then label 1 else check if score= value 2 then label 2 and so on. This would also save you a bunch of time if in the future the values compared against changed.

                       

                      Thanks,

                      Kent

                      1 of 1 people found this helpful
                      • 8. Re: Tableau Calculated Field - simpler way?
                        Rody Zakovich

                        I could be reading this wrong, but would this work?

                         

                        IF [Product] = 'ProductA' AND [H2 - Potential Tier Assignment Update] = "60-20-20" AND [% Attainment] < 0.5

                        THEN ((([% Attainment] - 0.5) * 100) + 1) * 88

                        ELSEIF [Product] = 'ProductA' AND [H2 - Potential Tier Assignment Update] = "45-40-15" AND [% Attainment] < 0.5

                        THEN ((([% Attainment] - 0.5) * 100) + 1) * 66

                        ELSEIF [Product] = 'ProductA' AND [H2 - Potential Tier Assignment Update] = "50-25-25" AND [% Attainment] < 0.5

                        THEN ((([% Attainment] - 0.5) * 100) + 1) * 110

                        END

                         

                        Just my shot at it, still could use improvements though

                         

                        Regards,

                        Rody

                        1 of 1 people found this helpful
                        • 9. Re: Tableau Calculated Field - simpler way?
                          Rody Zakovich

                          Actually, a "cleaner way" to do this is creating two calcs.

                           

                          [Potential Tier Assignment Value]

                           

                          CASE [H2 - Potential Tier Assignment Update]

                          WHEN "60-20-20" THEN 88

                          WHEN "45-40-15" THEN 66

                          WHEN "50-25-25" THEN 110

                          END

                           

                          Then simply create you wrapper function

                           

                          IF [Product] = 'ProductA' AND [% Attainment] > 0.5

                          THEN ((([% Attainment] - 0.5) * 100) + 1)  * [Potential Tier Assignment Value]

                          ELSEIF  [Product] = 'ProductA' AND [% Attainment] < 0.5

                          THEN 0

                          END

                           

                          Doing the same thing, just cleaner/more manageable if you have addition Tier that need to be added later

                           

                          Regards,

                          Rody

                          • 10. Re: Tableau Calculated Field - simpler way?
                            cignior

                            Hi Kent,

                             

                            I hear what you are saying. However, I'm not sure it is possible with what I am to accomplish. Here is the gist of my small project:

                             

                            I am attempting to create Bonus calculator. A user will login and only be able to see his/her territory. Each individual territory can be associated with either 60-20-20, 45-40-15, or 50-25-25 (Let's refer to these as tiers). Depending upon which tier the user is in, they receive a different bonus amount per percentage point.

                             

                            Example:

                            User 1 = 60-20-20 and gets paid based on Schedule A

                            User 2 = 50-25-25 and gets paid based on Schedule B

                            User 2 = 45-40-15 and gets paid based on Schedule C

                            .

                            ..

                            ... so and so forth.

                             

                            The schedules are a bit complicated to explain so I won't touch those. I do this Bonus calculator in excel and was able to concatenate many fields to provide an easy way to VLookup. However, I now want my users to be able to login and see the results for themselves.

                            • 11. Re: Tableau Calculated Field - simpler way?
                              Kent Sloan

                              Hi Cignior,

                               

                              You might be able to add the table that in Excel you were running the Vlookups against as a second data source in Tableau and do a blend against it to return the correct amount. The blend might slow things down a bit but would likely be faster than this calculation and be easier to maintain.

                               

                              Thanks,

                              Kent

                              • 12. Re: Tableau Calculated Field - simpler way?
                                cignior

                                I am attaching a sample twbx in hopes that it will paint a better picture.

                                • 13. Re: Tableau Calculated Field - simpler way?
                                  Rody Zakovich

                                  Ok one last shot at this, miss-read the formula before

                                   

                                  [Potential Tier Assignment Value]

                                   

                                  CASE [H2 - Potential Tier Assignment Update]

                                  WHEN "60-20-20" THEN 88

                                  WHEN "45-40-15" THEN 66

                                  WHEN "50-25-25" THEN 110

                                  END

                                   

                                  Wrapper Calc with MORE MATH!

                                   

                                  IF [Product] = 'ProductA'

                                  THEN ( ( ABS(((([% Attainment] - 0.5) * 100) + 1)) + ((([% Attainment] - 0.5) * 100) + 1)) ) / 2 )   * [Potential Tier Assignment Value]

                                  END

                                   

                                  This eliminates Tableau from having to do more comparative logic, and instead relies on Math to find the value. Which should be faster.

                                   

                                  Plus if you add tiers, you can simply add them to the First calc, and everything should adjust properly.

                                   

                                  Regards,

                                  Rody

                                  • 14. Re: Tableau Calculated Field - simpler way?
                                    cignior

                                    Hi Rody - perhaps you can take a look at the sample twbx I posted on my previous message.