12 Replies Latest reply on Aug 22, 2018 10:50 AM by Dan Cory

# [Question] Creating a breakdown in a total 100% bar based on a double conditional

if ([STUDENT_TYPE] = 'ALPHA' AND [CLASS] = 'A') THEN 'TRUE' ELSE

if ([STUDENT_TYPE] = 'ALPHA' AND [CLASS] = 'B') THEN 'FALSE' ELSE

if ([STUDENT_TYPE] = 'BETA' AND [CLASS] = 'B') THEN 'TRUE' ELSE

if ([STUDENT_TYPE] = ' BETA ' AND [CLASS] = 'A') THEN 'FALSE' ELSE

if ([STUDENT_TYPE] = 'CHARLIE' AND [CLASS] = 'A') THEN 'TRUE' ELSE

if ([STUDENT_TYPE] = ' CHARLIE ' AND [CLASS] = 'B') THEN 'FALSE' ELSE

if ([STUDENT_TYPE] = 'DELTA' AND [CLASS] = 'B') THEN 'TRUE' ELSE

if ([STUDENT_TYPE] = ' DELTA ' AND [CLASS] = 'A') THEN 'FALSE' ELSE Sorry for the paint picture here, but above is what I'm essentially trying to show in Tableau.  That snippet of Excel is a sample of the type of data, and the long if statement is the logic I'm using.  I want to show a total 100% breakdown bar for each student type, with what % is true and what % false, but I can't seem to figure it out.  When I create that calculated field, I actually only get "NULL" so I must have an error with that if statement as well, I'm guessing.  Any help would be greatly appreciated

• ###### 1. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

Is that logic what's actually in your calculated field?

The syntax is wrong, for one thing, which is why I ask.

• ###### 2. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

As of now yes it is, how would I go about fixing it?

• ###### 3. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

OK - you should have a bunch of red squiggly lines in the expression editor window and a big red error at the bottom - do you not have those?

ELSEIF is one word in Tableau syntax.

You've got extra spaces in some places - check that as it might cause other errors.

You need the word END after the last ELSEIF statement.

• ###### 4. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

Oh I'm so sorry!  So that was my first iteration if statement but I did end up resolving the elseif and added an END (my data is a mock up of what I am actually using), is there any other possible syntax error?  I ask because despite the statement passing the Tableau error test, it is still returning only Null, as far as I can tell.

• ###### 5. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

That makes more sense!

So this is not your real data, right?  There's likely something different about your real data that's getting in the way.

Can you make it work using this dummy data?

Alternatively, I'd start my debugging with one simple IF statement:

IF <Condition> Then TRUE

ELSE FALSE

END

And see what I get.

• ###### 6. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

Thank you! so I figured out that I was missing an underscore in the name which was returning nulls.... so now that I see the "True" and "False', what can I do to make it look like a 100% bar chart like in the picture/

• ###### 7. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

Stacked bars always work the same way: you drag the dimension you want to "stack" onto the Color mark.  So take your new True/False field (whatever it's called) and drag and drop it there.  You should get a single bar split into 2 segments.  Then click on the Color mark and pick Edit Colors to set the specific colors you want (orange and blue are the defaults, probably).

You will probably wind up with a vertical stack - just flip the pills in the Row & Column shelves or hit the "swap rows & columns" button on the toolbar to get it horizontal.

• ###### 8. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

Also, Tableau (and for that matter Excel) has full support for Booleans so you shouldn't put quotes around TRUE and FALSE. And you can use regular Boolean logic instead of IF / THENs.

It would be something like this:

([STUDENT_TYPE] = 'ALPHA' AND [CLASS] = 'A')

OR

([STUDENT_TYPE] = 'BETA' AND [CLASS] = 'B')

OR

([STUDENT_TYPE] = 'CHARLIE' AND [CLASS] = 'A')

OR

([STUDENT_TYPE] = 'DELTA' AND [CLASS] = 'B')

Finally, you can just create a set to do this all with UI.

Create a viz of STUDENT_TYPE and CLASS.

Select the pairs that belong to TRUE

Choose Create Set from the menu bar in the tooltip.

Now you can just put that set on color for your overall viz.

Dan

• ###### 9. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

What do I need, at minimum, in my columns/rows, for it to allow me to do a bar view?  If I just put the "Student Type" as a row, then drag the new calculated field onto Color, I just get two boxes and it won't allow me to select any bar type views.

EDIT:  If I make that new field a Measure, it defaults it to a count distinct, so each Student Type just has one bar next to it, that when highlighted, shows "2"

• ###### 10. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

You need Student Type on rows, Number of Records on columns, and the calculation (or set) on Color.

Dan

• ###### 11. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

Perfect thank you! I get the bars like I want them, but is there a way to make all the bars an equal length and display the percent of each  on there?  AKA the % of TRUE and % of false for each?

• ###### 12. Re: [Question] Creating a breakdown in a total 100% bar based on a double conditional

Use the "Percent of Total" quick table calculation to get equal length bars. You will want Percent of Total by Student Type.

Also put the same calculation on text.

Dan