5 Replies Latest reply on Jan 15, 2013 8:33 AM by Chaya Gordon Burstyn

# Putting a range of numbers in an IF statement

I need help with a calculation. I want to either put a range of numbers in an IF statment or do an AND in an IF statment.

Basically, I am trying to create a new Measure that would indicate sale price.  My current calculation for my Measure for 50% off is:

IF [ITEM_PRICE String]="2.5000" then [Scan Quantity Period] End

This calcuation is working for me,  however, I do have one "ITEM_Price String"  that is 2.6000 but should also be included.  So I eather need a range in the IF statement where it would be = 2.5000 through 2.6000 or I need an AND or a SUM with a second IF statement that is =2.6000

Any help with this would be greatly appreciated.

Thank you!

• ###### 1. Re: Putting a range of numbers in an IF statement

I just realized a range of numbers would probably work better for me than multiple IF statements since I have more than 2 values that I want to include, I would like it to range from 3.8000 to 4.6000.

Thanks.

• ###### 2. Re: Putting a range of numbers in an IF statement

Tableau supports AND, OR, and the NOT operator, so I'd write your formula something like:

IF [ITEM_PRICE String]>= "2.5000" AND  [ITEM_PRICE String] <= "2.6000" THEN

[somevalue]

ELSEIF  [ITEM_PRICE String]>= "3.8000" AND  [ITEM_PRICE String] <= "4.6000" THEN

[some other value]

END

A couple of notes: The calc as set up will return Null for everything that doesn't fall in the range. Also, according to Tableau developers string operations are several to 100x slower than numeric operations. If you have a numeric ITEM_PRICE, I suggest you use that instead.

Jonathan

1 of 1 people found this helpful
• ###### 3. Re: Putting a range of numbers in an IF statement

I got it to work withusing an OR and just putting in all the numbers.

I originally tried my calcuation with Item Price as a # (it calls it a Float) using the below formual and I got this error "cant' compare float and string values)

So I converted the Item Price to a string, but I would actually much prefer to use it as a number.  Any ideas why I am getting that error and if there is a better way to right that formual using the numeric value for item price?

Thank you.

IF [ITEM_PRICE]="2.5000" then [Scan Quantity Period] End

• ###### 4. Re: Putting a range of numbers in an IF statement

The way I understand your post, [ITEM_PRICE] is a number. Therefore, I believe the comparison should be:

IF [ITEM_PRICE]=2.5 THEN [Scan Quantity Period] END

In Tableau, numbers are written out as is, strings get quotes around them "string", and dates have # signs.

Jonathan

• ###### 5. Re: Putting a range of numbers in an IF statement

Perfect, thank you.