-
1. Re: how to convert excel formula to tableau
Nathan Panuco May 10, 2018 9:49 AM (in response to RENATO FONSECA)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 RuncMay 10, 2018 10:01 AM (in response to RENATO FONSECA)
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 OssaiMay 10, 2018 3:30 PM (in response to RENATO FONSECA)
2 of 2 people found this helpfulHi 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
-
4. Re: how to convert excel formula to tableau
Simon RuncMay 11, 2018 12:35 AM (in response to Okechukwu 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)
-
5. Re: how to convert excel formula to tableau
Okechukwu OssaiMay 11, 2018 1:00 AM (in response to Simon Runc)
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 May 11, 2018 9:33 AM (in response to Nathan Panuco)Thanks for taking a time with my problem. Your answer was very valuable.
-
7. Re: how to convert excel formula to tableau
RENATO FONSECA May 11, 2018 9:33 AM (in response to Okechukwu Ossai)Thanks for taking a time with my problem. Your answer was very valuable.
-
8. Re: how to convert excel formula to tableau
RENATO FONSECA May 11, 2018 9:34 AM (in response to Okechukwu Ossai)Thanks for taking a time with my problem. Your answer was very valuable.
-
9. Re: how to convert excel formula to tableau
RENATO FONSECA May 11, 2018 9:34 AM (in response to Simon Runc)Thanks for taking a time with my problem. Your answer was very valuable.
-
10. Re: how to convert excel formula to tableau
RENATO FONSECA May 11, 2018 9:34 AM (in response to Simon Runc)Thanks for taking a time with my problem. Your answer was very valuable.
-
11. Re: how to convert excel formula to tableau
Okechukwu OssaiMay 11, 2018 10:10 AM (in response to RENATO FONSECA)
You're welcome Renato. Glad it helped.