8 Replies Latest reply on Mar 6, 2012 7:47 AM by allysonwinters2

# If statement calculation

Hi everyone,

I would like to calculate the % of claims that for which the following field (Initial Reserve in Hours) is less than 72 hours for each salesperson out of the total of all claims.  My results are either blank or 100%.  This cannot be right there should be a value good or bad for every salesperson.  here is the formula I am using:

IF(SUM([InitialReserveInHours])<72 )then SUM([ClaimID])end/sum([ClaimID])

Thanks

Allyson

• ###### 1. Re: If statement calculation

Hi again

Attached is a screen shot of the underlying data for one of the salespeople.  This person has a blank on my workbook.  As you can see the field for initial reserve in hours has four lines (claims) which are under 72 hours, yet the result is shown as a blank.

Allyson

• ###### 2. Re: If statement calculation

Hi Allyson,

Would you be able to post the workbook (saved as a twbx file)? It is hard to say why the calculation is not returning the correct information without seeing if there are any filters, how the view is set up, etc.

Thanks!

-Tracy

• ###### 3. Re: If statement calculation

Hi Tracy

I would do that but the extract would be huge.  The data is made up of multiple data tables, also I do not want to publish company information.

Allyson

• ###### 4. Re: If statement calculation

not in front of Tableau but would this work:

1st, create a calc field "X" as

IF [InitialReserveInHours]<72 then 1 else 0 end

then another calc field as SUM([X]) / [NumberOfRecords]

Bruce

• ###### 5. Re: If statement calculation

Please find attached what I think the solution is. I have created a random file with each row a case by the sale person. 2 columns: saled person id and the reserve time. You can change via a parameter the cutoff point. Does this work for you?

If your data is structured differently, you could easily change it. Hope that helps.

Best.

• ###### 6. Re: If statement calculation

Bruce - just saw that I've posted the same answer. Sorry - did not wanted to steal the thunder

• ###### 7. Re: If statement calculation

Hi Bruce,

I am getting one of my favourite errors with the second formula.  "Arguement to sum is already an aggregation..."

Allyson

• ###### 8. Re: If statement calculation

Hi Rasvan

I tried to open your workbook but there did not seem to be any data.