8 Replies Latest reply on Jun 29, 2012 10:09 AM by Elizabeth Coleman

# Calculation Using IF And

Is it possible to create a calculation to compare a string and integer?  Here is an example of what I'm trying to do:

If Type = O and If Count = 1 then 1

• ###### 1. Re: Calculation Using IF And

I want to convert the string to a number so I can use it in a calculation.  Is it possible, if so can someone give me an example.

• ###### 2. Re: Calculation Using IF And

Hi Elizabeth,

If Count is a field name, then the above calculation should work by making it look like the following:

if [Type]='O' and [Count]=1 then 1 end

However, if count is being used as an aggregation on a different field, then Type will also need to be aggregated, so the calculation should look similar to the following:

if attr([Type])='O' and Count([Type])=1 then 1 end

Hope this helps!

-Tracy

• ###### 3. Re: Calculation Using IF And

Tracy,

Thanks for responding to my question but I'm not getting the results I expected.  I'm getting Null when I tried to use the formula that you posted. The first formula states  "can't compare integer and string values".

When I used the second formula I'm getting Null.

• ###### 4. Re: Calculation Using IF And

Would you be able to post the workbook (twbx file) or a screenshot or two?

-Tracy

• ###### 5. Re: Calculation Using IF And

Tracy,  Here is a sample file.  On sheet 2 it shows the count of orders for Forecast and Moved Out.  In the raw data there are multiple rows for the same sales order.  I identified the sales orders by using a sumproduct in my Excel file Moved Out worksheet in the column labeled 'Count'.  If there is a 1 then it's one instance of the sales order; if there is a 2, 3, or 4 then it a duplicate depending on how many times the sales order is in the month.   I need to somehow identify and eliminate the duplicate sales orders from the count of sales orders by month.

• ###### 6. Re: Calculation Using IF And

Your original spreadsheet was not included in your zipped file (just your Tableau extract from that spreadsheet). Since you seem to have the ability to modify your original spreadsheet, why not simply add a column to that spreadsheet, which would identify the rows you want to use.

Ex, assuming you are in row 2; [Type] is column A; and [Count] is column B. Add a column with the Excel formula:

=if(and(A5="O",B5=1),1,0)

Call the new column something like bCountRow and then in Tableau simply filter for bCountRow=1.

• ###### 7. Re: Calculation Using IF And

[Count] is being held as text, not a number:

if [Type]='O' and [Count]='1' then 1 end

Would work. Or you could just use both Type and Count on the filter shelf

• ###### 8. Re: Calculation Using IF And

Thank you that solved my problem.