5 Replies Latest reply on Jul 16, 2013 8:30 AM by Alex Kerin

Multiple/overlapping values in a case statement?

Amy Song Jan 18, 2012 11:29 AM

Hi, I have dimensions that I would like to group into different groups but the issue is that some may need to be in multiple groups. For example, my data:

Dimension

In SQL, I can put different combinations of them in groups such as:

case when dimension in ('a','b','c') then 'Group 1'

when dimension in ('b','c','d') then 'Group 2'

wen dimension in ('a','e') then 'Group 3' else null end

...etc

However it looks like the case statement in Tableau does not allow for multiple values, and I was unable to use the if statement since the subsequent statements call for "elseifs" which mutually excludes the values. Please let me know if I am missing an obvious syntactical method to do this! Thanks! :)

1. Re: Multiple/overlapping values in a case statement?

Dimitri.B Jan 18, 2012 5:42 PM (in response to Amy Song)

Tableau will be happy to run with the same structure as your SQL example, however in both SQL and Tableau each item will be placed in only one group - the first relevant one in the statement's order. So in your example 'b' and 'c' will belong only to 'Group 1', and not 'Group 2'. Same goes for IF statements, as you correctly observed.

I don't think there is any way around this in Tableau, and logically, if successful, this approach will produce duplicates.

One way to hack it is to deliberately duplicate the source data (self-join?) and then you will have several 'a' to play with and put in different groups. I don't know what the outcome will be, though.
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Multiple/overlapping values in a case statement?

Eric Maher Feb 2, 2012 4:05 PM (in response to Amy Song)

I am attempting to do something very similar and posted a question today:
http://community.tableau.com/thread/116042
I thought I had it worked out until I attempted to use the IN statement.

If you find a solution I would be very interested to know what you've done.
Like Show 0 Likes(0)

Actions
3. Re: Multiple/overlapping values in a case statement?

Ey Tan Jul 15, 2013 8:48 PM (in response to Eric Maher)

Hello there,

I am trying to do something similar. Not sure if anyone has a workaround to this?

thanks.
Like Show 1 Likes(1)

Actions
4. Re: Multiple/overlapping values in a case statement?

Amy Song Jul 16, 2013 8:23 AM (in response to Ey Tan)

Hey, I posted that a while ago and has since learned a bit more about the functionalities, and Dimitri is absolutely right that that's not possible with a case statement. Depending on your objective, I've found I could use parameters to achieve overlapping groupings.

One example I've used it in is filtering for year to date data... so if I select January, I only want to see January, if I select February I want to see Jan+Feb etc. To do this, I created a parameter list for months (as int, with the "show as" piece as the name), and then a calculated field that says [parameter]<=month([date]).
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
5. Re: Multiple/overlapping values in a case statement?

Alex Kerin Jul 16, 2013 8:30 AM (in response to Amy Song)

It's also likely that you could use custom SQL and a cross join to get the data you need. kettan may be able to help.
Like Show 0 Likes(0)

Actions