11 Replies Latest reply on May 11, 2018 10:10 AM by Okechukwu Ossai

    how to convert excel formula to tableau

    RENATO FONSECA

      Hi every one. I need help urgent.

      We have this excel formula in our data base, but I could not reproduce this formula bellow in tableau's calculate field. Can someone help me?

       

      =IF(IF(IF(AND(IF([ORIGEM]="1";"SIM";IF([ORIGEM]="6";"SIM";"NÃO"))="SIM";IF([UF_CLIENTE]="PR";IF([UNIDADE]="10";"SIM";"NÃO");"NÃO")="SIM";IF(OR([TIPO_NOTA]="1";[TIPO_NOTA]="N";[TIPO_NOTA]="2";[TIPO_NOTA]="3");"SIM";"NÃO")="SIM");"SIM";"NÃO")="SIM";(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4)-9.25%*(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4);0)>0;IF(IF(AND(IF([ORIGEM]="1";"SIM";IF([ORIGEM]="6";"SIM";"NÃO"))="SIM";IF([UF_CLIENTE]="PR";IF([UNIDADE]="10";"SIM";"NÃO");"NÃO")="SIM";IF(OR([TIPO_NOTA]="1";[TIPO_NOTA]="N";[TIPO_NOTA]="2";[TIPO_NOTA]="3");"SIM";"NÃO")="SIM");"SIM";"NÃO")="SIM";(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4)-9.25%*(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4);0)%*[BASE_ICMS];0)

       

       

      THX for any help.

        • 1. Re: how to convert excel formula to tableau
          Nathan Panuco

          I do not believe that anyone will be able to take that formula and output what you are looking for - you will likely need to take some time to recreate the functionality you desire.

           

          You'll need to understand formulating calculations in Tableau: https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#functions_operators.html%3FTocPath%3DDesign%2520Views%…

          This article on "Tableau for Excel users" might also be useful: https://www.theinformationlab.co.uk/2012/03/27/tableau-for-excel-users-part-2-calculated-fields/

           

          Hope this gets you in the right direction!

          • 2. Re: how to convert excel formula to tableau
            Simon Runc

            Wow what a formula! and not sure how you've ended up with an Excel formula in a database!

             

            While I won't attempt to unpick this, the main difference in Syntax between Excel IF statements and Tableau's are that Tableau uses the form

             

            IF<condition>THEN<do this if condition is true>ELSE <do this if condition is false>END (using those key words)

             

            and you can also do further ELSEIF rather than need to nest multiple IFs

             

            so IF...THEN...ELSEIF...ELSEIF...ELSE...END

             

            whereas Excel uses IF(<condition>,<do this if condition is true>,<do this if condition is false>)

             

            Also in Tableau you put the AND/OR between multiple conditions, rather than use AND and then list the conditions (separating them with a comma, or semi-colon depending on your regional settings), so something like

             

            IF [Sales] >0 AND [profit]<0 THEN...

             

            Hopefully this will help you reproduce your statement in Tableau form...best of luck!

            • 3. Re: how to convert excel formula to tableau
              Okechukwu Ossai

              Hi Renato,

               

              This seems like mission impossible but I'm happy to take a ride!

               

              The formula is very complicated. You are in a better position to understand it since you have access to the spreadsheet or database. I've done my best to unpack the code into tiny pieces. My main aim is not to deliver a perfect solution but at least provide a good foundation to nudge you in the right direction towards achieving your goal. You would have noticed that named cells were used in the Excel formula instead of direct cell references. So, you need to find out what those named cells mean.

               

              Another important observation is the use of '%' in the code. Tableau will not accept that. I don't even know what that means. I've left them in the codes but you must remove them or replace them with the appropriate value before implementing the codes in Tableau. If the '%' signs don't have any significance then delete them, but if they mean real percentage then replace them with ( * 0.01)

               

              The intimidating formula can be broken down into these smaller chunks.

               

              Step 1: Create calculated field [Code 1]

              IF [ORIGEM] = "1" OR [ORIGEM] = "6" THEN "SIM" ELSE "NÃO" END

               

              Step 2: Create calculated field [Code 2]

              IF [UF_CLIENTE] = "PR" THEN

                   IF [UNIDADE] = "10" THEN "SIM"

                   ELSE "NÃO" END

              ELSE "NÃO" END

               

              Step 3: Create calculated field [Code 3]

              IF [TIPO_NOTA] = "1" OR [TIPO_NOTA] = "N" OR [TIPO_NOTA] = "2" OR [TIPO_NOTA] = "3" THEN "SIM" ELSE "NÃO" END

               

              Step 4: Create calculated field [Code 4]

              IF [Code 1] = "SIM" AND [Code 2] = "SIM" AND [Code 3] = "SIM" THEN “SIM" ELSE "NÃO" END

               

              Step 5: Create calculated field [Code 5]

              ([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]%)) - 4) - 9.25% * (([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]%)) – 4

               

              Step 6: Create calculated field [Code 6]

              IF [Code 4] = "SIM" THEN [Code 5] ELSE 0 END

               

              Step 7: Create calculated field [Code 7]

              [Code 6]% * [BASE_ICMS]

               

              Step 8: Create calculated field [Final Code]

              IF [Code 6] > 0 THEN [Code 7] ELSE 0 END

               

              Remember to delete all '%' signs or replace them with the appropriate values.

               

              I hope this works or at least helps you in understanding the internal structure of the formula. With this understanding, I expect that you can easily tweak your way to the solution you desire.

              Ossai

              2 of 2 people found this helpful
              • 4. Re: how to convert excel formula to tableau
                Simon Runc

                Bravo Ossai!...very impressive

                 

                with regards the % sign...if memory serves me correctly (not really used Excel for a while) the % sign just changes that value into a percentage, so /100 would do the same thing. % is used in Tableau, as the Modulus Operator (so returns the remainder of a division), for example 3%2 = 1 (in Excel this would be =MOD(3,2)

                • 5. Re: how to convert excel formula to tableau
                  Okechukwu Ossai

                  Thanks Simon. I thought as much regarding the % sign but couldn't make my mind up.

                   

                  Renato,

                   

                  It's most likely the % signs are percentages, so make the following changes to [Code 5] and [Code 7].

                   

                  Step 5: Create calculated field [Code 5]

                  ([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]*0.01)) - 4) - 9.25*0.01 * (([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]*0.01)) – 4

                   

                  Step 7: Create calculated field [Code 7]

                  [Code 6]*0.01 * [BASE_ICMS]

                   

                  Hope this helps.

                  Ossai

                  • 6. Re: how to convert excel formula to tableau
                    RENATO FONSECA

                    Thanks for taking a time with my problem. Your answer was very valuable.

                    • 7. Re: how to convert excel formula to tableau
                      RENATO FONSECA

                      Thanks for taking a time with my problem. Your answer was very valuable.

                      • 8. Re: how to convert excel formula to tableau
                        RENATO FONSECA

                        Thanks for taking a time with my problem. Your answer was very valuable.

                        • 9. Re: how to convert excel formula to tableau
                          RENATO FONSECA

                          Thanks for taking a time with my problem. Your answer was very valuable.

                          • 10. Re: how to convert excel formula to tableau
                            RENATO FONSECA

                            Thanks for taking a time with my problem. Your answer was very valuable.

                            • 11. Re: how to convert excel formula to tableau
                              Okechukwu Ossai

                              You're welcome Renato. Glad it helped.