I did it using the following calculated fields
INT( LEFT( [Amount - Dimension], FIND( [Amount - Dimension], " - " ) - 1 ) )
MID( [Amount - Dimension], FIND( [Amount - Dimension], " - " ) + 3 )
Example is attached.
Book2.twbx.zip 12.8 KB
This is great. Now I have a very basic functionality question?
Why do we include the -1 (INT( LEFT( [Amount - Dimension], FIND( [Amount - Dimension], " - " ) - 1 ) )) in the calculations that you mentioned?
And the Mid function you mentioned displays characters from the start position mentioned to the desired length. So in my example, if SFDC has 4 characters, how does the +3 work?
I know these are basic questions for you, but it will really help me understand the way these work in Tableau
1 of 1 people found this helpful
Both the -1 and +3 are arithmetic additions to the value of FIND(). FIND() returns the location of the first occurence of " - ". Let me give you an example
String: 1000 - SFDC
FIND( [Amount - Dimension], " - " ): 5
Last value of Amount: 4
Therefore, we need to use -1 to get rid of the first character of " - ".
The reverse logic applies for the dimension. Instead of subtracting 1 to remove the first character, we add 3 to skip the entire " - " substring, then we start from FIND() + 3, which is the first character of SFDC.
Hope this helps,
That is perfect Brad
You have a really clean way of understanding logic.
Appreciate your help