It's always tricky to see what's going on when you're using multiple dimensions in the condition. For your example you're using Hierarchy, Account and Spend Category dimensions, so the order might have an impact.
Check your input and output though and remember that it will return the first value that is true.
So in your example, if Hierarchy is 'Other professional' then the result is Professional (it doesn't evaluate any further). If it's not 'Other professional' then check the Account values to evaluate to 'Travel'. If it's not that, then start checking the Spend Category values.
If that doesn't help, or you already know this, the only other thing I'd recommend would be to bracket the condition, so change :
ELSEIF [Account] = '5030' OR [Account] = '5040' THEN "Travel"
ELSEIF ([Account] = '5030' OR [Account] = '5040') THEN "Travel"
Thanks for following up. I can see what you're saying about the order of
ELSEIF statements. It appears that regardless of which way I switch the
order it adds values to to categories I wouldn't expect. Any thoughts on a
case statement or another approach? I'm essentially trying to create
filtered groups that assess the whole data source.
On Tue, May 9, 2017 at 9:39 PM, Chris McClellan <
You need to use nested IF statements. It will be difficult to offer a good solution without seeing how your data is structured, at least the hierarchy relationship between the dimensions in the formula may provide a little insight to where the problem might coming from.
Thank you for your response. The underlying data is roughly 400,000 rows
and contains just about every value imaginable from the data source its
being pulled so its not easily shareable or changed to fake the data. Does
it help to provide the order in which the columns appear in the source?
2. Spend Category
I took the previous advice and tried structuring it slightly differently
based on that order and it worked successfuly for 6 out of 9 groups. Any
thoughts would be greatly appreciated. Thank you.
On Tue, May 9, 2017 at 10:25 PM, Okechukwu Ossai <
With the logic you posted, this is the results.
Even with 400,000 records, it would be good to see your Account, Spend Category, Hierarchy combinations ONLY (ie just those 3 columns) and the results that are right/wrong.
235668.xlsx 9.8 KB
Please see attached and thanks for your help so far. It's not the quantity of data but the type. I've tried my best to replicate what is going on and I think this does the trick:
In the calculation: 'Professional Services' equals $755,075
In the spreadsheet: 'Services' under 'Hierarchy' equals $756,175
If you isolate the calculation from the ELSEIF then 'Professional Services' is correct. This is what I'm running into. Any thoughts?
ELSEIF.twbx 144.7 KB
If you create a quick worksheet with the values of Account, Professional Services, and Calculation1, filtered only to those records where Professional Services = 'Professional Services' and Calculation1 does not, you can see what is happening:
You can see that all the records where Calculation1 is not Professional Services have one of 3 different Account values. All of these values of Account are specified within Calculation1 above where you're classifying things into Professional Services:
So the question is, which of these should take priority? If Account=5040 but Hierarchy = 'Professional Services', should Calculation1 = Travel, or Professional Services?
In your database there are some accounts which are 5130 but classified as Travel and Professional Services. Your formula assigns all 5130 account as Information Technology. Is this what you want to do?
I'm not sure I follow. Are you saying that based on which account number I choose to take priority that something else will always be falling out because the calculation assesses on a row level basis? Is there a way to isolate each so Tableau assesses the whole data set over and over?
Thank you. I appreciate it!
Yes that's fine. I had to scrub alot of the detail to share this data so I
know some of it might not make sense of context. Is there a way to isolate
each grouping so the calculation reads 1 line assess each row in the data
source goes to the next and assesses each row in the data source? Is LOD a
On Wed, May 10, 2017 at 10:00 AM, Okechukwu Ossai <
Using your example below.
Services is 756,175 from the spreadsheet. However, there are few inconsistencies in Calculation1. You have multiple assignation for some of the accounts. For example account 5040, 5110 and 5130 have been assigned to Travel, IT and Professional Services. These accounts cannot live in more than one category at the same time, that is why Calculation1 is returning incorrect result.
Yes, the calculation is working at row level. So because you're using ELSEIFs, for each record, the calculation will work through all of your ELSEIF conditions in order until it hits one that is true, and use that one to assign the value.
So, for example, for the records where Account=5040 and Hierarchy=Services, the calculation will go something like:
IF [Account] = '5030' THEN 'Travel' Does Account=5030? No, so let's carry on...
ELSEIF [Account] = '5040' THEN 'Travel' Does Account=5040? Yes it does, so the value is Travel. Stop here.
The rest of the calculation will be completely ignored, because it's found a condition that is true.
You can see how the formula is evaluating if you do something like this:
Thank you both for these explanations. Are you basically saying there is no way to go the desire result? Any suggestions on a different approach?