Great!!. I liked the way you turned it around. Learned a new trick today from you!!!
Thanks Norbert, but my actual datafile already has a parameter (whether you want to see group names or leader names) and then filters (from group head to their direct reports), so I was hoping to do it as a calculated field rather than adding another filter/parameter..
Thanks Joe. Just one more question, what about when there is additional condition to the if statement,
IF attr([Group Leader 2])="GH 1" AND ATTR([Job Level])="CEO" then 0.1
ELSEIF attr([Group Leader 2])="GH 1" AND ATTR([Job Level])="COO" then 0.2
IF attr([Group Leader 2])="GH 2" AND ATTR([Job Level])="CEO" then 0.4
ELSEIF attr([Group Leader 2])="GH 2" AND ATTR([Job Level])="COO" then 0.5
and I want to show each of the overall goal for CEO and COO in aggregate, is this possible?
thanks for your help!
In the attached I simulated what you did, but for L07 and L08 from [Job Level Group]. I narrowed it down to those two.
I have to have that dimension on the sheet to separate out the values, otherwise the logic goes into the "all other" bucket because for any "GH 1" there are multiple values and tableau just says, "huh?".
THe logic in [Goal] can get as complicated as you need it to be. Maybe the value for one level is always one bump up from the lower level, for instance. For now I just used the sledgehammer and spelled out each condition, but maybe the algorithm can be more elegant.
Diversity sample B.twbx 33.6 KB
But I have, in this example, separate goals for overall L07 and L08. So I want to add at the end,
if (all group leader) is selected, AND job level is L07 then 0.9
elseif (all group leader) AND job level is L08, then 0.99
something like that.
I tried at the end, and at the beginning,
elseif job level=L07 then 0.9
elseif job level=L08 then 0.99
(so didn't include group leader condition) but it doesnt work can you help?
Yes! I can help!
Check out the attached.
There are actually multiple ways to approach this.
First of all, I changed [Goal] to do what you are looking for -- in the context of the data we have on hand. (Ignore the 4 beneath the value for now.)
But I notice if we select all Job Levels (give it a try) we get displays for all the levels, and maybe you don't want that. (Maybe you do, and we're done here!)
You can do some things to know what "All" really represents, and whether or not your user has selected "all". (For the record, "All" isn't an actual value in Tableau. It's an added in function of convenience for your users so that they don't have to select 100 boxes if there are actually 100 choices in the filter list. See Sheet 1a. In there I customized the Job level filter to remove the "All" value. That's what the filter choices really are as defined by your data.)
Anyway, in sheet 1 I added a calc -- [Count Group Leader 2 in ALL]. This is using LOD to see how many actual Leader2 values are in your data source. A FIXED calc evaluates before any other filters activate, so you get your count of data as it comes into Tableau. And then I created a COUNTD LOD calc to count what is actually selected on the sheet. This tells tableau to do a countD, but ignore the partitioning across [Job Level]. Essentially do a COUNTD across the whole sheet. I put these in the title so you can see what's happening with those numbers.
On Sheet 2 I created a new filter to replace the quick filter on Job Level Group. This is a table calc. And when you use a table calc, the filter doesn't eliminate rows from your table. It just limits what part of the table gets displayed. It still works like a filter, and you can even display it like a quick filter. This may or may not be important to your situation, but your actual application and data may need this.
Sheet 2 is still using the [Goal] calc that I modified.
Now look at Sheet 3.
I made a copy of [Goal]. In this one I used the two new COUNTD calcs to determine whether or not ALL was selected for Group Leader. This time I put the check for that portion of the logic at the top. Just because...
Anyway, I did this to show you ways to handle what you need to do. The COUNTD method will be useful to you in lots of places. You can know what the user has selected, and act accordingly.
Diversity sample C.twbx 40.5 KB
Thank you so much Joe, you are a huge help!
Although, my '[Count Group Leader 2 in ALL]' wasn't working, it kept changing as the group leader filter changes, so I just did
IF [Count Group Leader selected] = actual number of leaders, since it doesn't change. It's not ideal, but this will do.
Thanks again Joe!