10 Replies Latest reply on Aug 20, 2018 2:49 PM by Marc Ybarsabal

# How to create bins - If/Then or Case?

Hello,

I'm trying to create bins for a float calc but having trouble using an If/Then because it ignores the later bins. I tried a Case statement but it's just not working for me.  Could someone take a look and help me create the bins described on the dashboard (or at least one or two as an example)

Thanks,

• ###### 1. Re: How to create bins - If/Then or Case?

Hi Matt, does the set of bins absolutely have to be the set specified? If you're willing to use equally sized bins, you can just use Tableau's built-in bin functionality.

Otherwise, the quickest way to build bins in this situation is to use groups. Right-click the measure > Create > Group. Then, in that window, select the integers you want in each bin, press "Group", and name each group as you see fit.

Another way of tackling this (which is especially useful if there are way too many unique numbers in the measures or if they change frequently) is to set up Tableau built-in bins and then create sets on those bins. Let me know if you need to try to implement that solution.

EDIT: Now that I think about it, you can also create a custom calculation that rounds to the nearest 25 (or gets the nearest integer quotient when dividing by 25) and then set up a case statement using those values.

1 of 1 people found this helpful
• ###### 2. Re: How to create bins - If/Then or Case?

Something like this should work. An IF statement works fine... you just need to do ELSEIF for each of the additional arguments after the first. Check out the attached workbook and let me know if you have questions.

• ###### 3. Re: How to create bins - If/Then or Case?

Russ Lyman thanks for helping. Could you save in 9.2 so I can open it though!?

• ###### 4. Re: How to create bins - If/Then or Case?

David Li, thanks for that. I didn't realize Tableau has a bins function but actually, to your last point, I want to define the bin size. I was having trouble making a Case statement for this. The error message was just "wrong syntax" but I couldn't find anything wrong.

• ###### 5. Re: How to create bins - If/Then or Case?

I didn't open Russ's file, but you can probably just do something like:

IF [The Measure] <= 25 THEN "1-25"

ELSEIF [The Measure] < 50 THEN "26-50"

ELSEIF [The Measure] < 100 THEN "51-100"

ELSE ">100"

END

EDIT: If you post the CASE statement you used, we may be able to troubleshoot it. But CASE probably won't work here because you're looking at ranges of values.

• ###### 6. Re: How to create bins - If/Then or Case?

Here you go:

IF [Interval between First and Subsequent Purchase (SUM)] <= 50.0 THEN "1-50"

ELSEIF [Interval between First and Subsequent Purchase (SUM)] <=75.0 THEN "51-75"

ELSEIF [Interval between First and Subsequent Purchase (SUM)] <=100.0 THEN "76-100"

ELSEIF [Interval between First and Subsequent Purchase (SUM)] <=150.0 THEN "101-150"

ELSE "Fill in Rest..."

END

• ###### 7. Re: How to create bins - If/Then or Case?

Russ Lyman Thanks!

• ###### 8. Re: How to create bins - If/Then or Case?

David Li does Case work with numbers? That was my question.

• ###### 9. Re: How to create bins - If/Then or Case?

Matt, CASE does indeed work with numbers, but since it's a type of switch, it can only match against one value at a time. It can't match a range (unless the range has been converted down to one value, as would be the case if you were to divide everything by 25 and then truncate).

• ###### 10. Re: How to create bins - If/Then or Case?

I'm not sure this meets your needs, but here is how I created a "Bin" whose size is defined by a parameter.

1. Create a parameter [Bin Size] that is an integer 1-10 (choose appropriate range).

2. Create a calculated field with this: STR( FLOOR( [measure to bin] / [Bin Size] ) * [Bin Size] )