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!

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!

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

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)

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

You're welcome Renato. Glad it helped.