13 Replies Latest reply on Sep 29, 2016 1:48 PM by heather.vogt

# Calculation error?

Hello! Can someone help me figure out what's wrong with my variable-sized bin?

I am trying to bin regions of the world into sales categories--High, Medium and Low. The High bin will bin regions with >=\$500,000 in sales. Medium is for sales between \$499,999-250,000 and, finally, Low is for anything <=\$249,999.

I used the calculation editor to create a Region Bins variable-sized bin, but when I go to use it, only the Medium measure applies to the viz. I've gone through the calculation to see if I missed anything but nothing catches my eye. Any ideas?

I attached the workbook. The sheet in question is 15-16 Sales by Regions.

• ###### 1. Re: Calculation error?

If you change your formula to this it will work:

IF SUM([Sales])>=500000

THEN 'High'

ELSEIF SUM([Sales])<=249999

THEN 'Low'

ELSE 'Medium'

END

1 of 1 people found this helpful
• ###### 2. Re: Calculation error?

See Sheet 16

1 of 1 people found this helpful
• ###### 3. Re: Calculation error?

Thank you!

• ###### 4. Re: Calculation error?

Thank you!

• ###### 5. Re: Calculation error?

Hi Heather,

1) Modify the calculated field logic so that the IF-ELSEIF-ELSE ladder follows basic binning criteria starting bottom to top and uses logical operator AND

IF SUM([Sales]) <= 249999

THEN 'Low'

ELSEIF SUM([Sales]) > 249999 AND SUM([Sales]) <=499999

THEN 'Medium'

ELSEIF SUM([Sales]) >= 500000

THEN 'High'

END

2) Use aggregate for Sales, it is the norm to be used for measures when used in calculated fields.

Here is the updated graph:

Hope that helps!

Thanks,

Dhanashree

1 of 1 people found this helpful
• ###### 6. Re: Calculation error?

Thank you! This is awesome!

• ###### 7. Re: Calculation error?

Thanks for your help and prompt responses! Can you tell me why adding the Medium statements mess the whole thing up? Even if I repeat the exact same syntax and only change the numbers and label, the calc messes up. Is this a Tableau thing?

• ###### 8. Re: Calculation error?

I'd change again to make it clearly:

IF          SUM([Sales]) < 250000 THEN 'Low'

ELSEIF SUM([Sales]) < 500000 THEN 'Medium'

ELSE                                                       'High'

END

• ###### 9. Re: Calculation error?

Basically, the medium logic needs to take into consideration the minimum and maximum values, the BETWEEN aspect. I used AND operator for the same. It is not Tableau thing Heather.

Let me know if you have any other questions.

• ###### 10. Re: Calculation error?

Ah! The calculations you guys so happily offered up are working fine. Is this the better way to do it? Is there a best practice for how the calculation should be ordered? If so, do you have a resource for that--whitepaper, website, etc.?

Again, why do you need to leave the "High" range out of it? I get that the Low and Medium ranges take care of it because everything else not fitting the low and medium is "High," but why is it wrong to put those High ranges in there? When they aren't in there, the calculation works; when they are (like what I had originally +SUM) it doesn't work. Why is that?

• ###### 11. Re: Calculation error?

Because is implicit condition SUM([Sales]) >= 500000 on Else Clause.

• ###### 12. Re: Calculation error?

To understand why the original calculation wasn't working, you have to know how the computer thinks. So let's take a look at that calculation:

IF[Sales]>=500000

THEN 'High'

ELSEIF[Sales]<=499999

THEN 'Medium'

ELSEIF[Sales]<=249999

THEN 'Low'

END

Tableau will read this calculation one statement at a time, starting at the beginning. First, it will check to see if Sales is greater than or equal to 500000. If this statement is true, it stops reading the calculation and will return High. If this statement is false, it continues to see if Sales is less than or equal to 499999. Obviously, if sales is NOT greater than or equal to 500000, then it MUST be less than or equal to 499999. Therefore, Tableau will stop reading here and return Medium for all values less than 499999, even those less than 249999. This is why Dhanashree included that AND clause in her solution; so that BOTH conditions needed to be satisfied in order for Tableau to return Medium.

So you can include all 3 ranges in there, as Dhanashree did, but you need to make sure all of these ranges are mutually exclusive. That is, there is absolutely no overlap. Clearly, without the AND statement, you would have overlap (as all observations <=249999 are also <=499999).

1 of 1 people found this helpful
• ###### 13. Re: Calculation error?

Oh! Now I see! Thanks!

I guess this will be something I make sure to mention in my Calculations section.

Thanks again! You guys are great!