8 Replies Latest reply on Nov 2, 2012 12:45 PM by Alex Kerin

# how to round up to 1 from anything less than 1?

I have a chart containing a calculated field [Quarterly Predicted Failure Rate] which when rounded using the automatic formatting displays a bar as zero but I want it to round up to 1 if it is less than 1.

The formula for [Quarterly Predicted Failure Rate] is [Number of Records]/3*8760/[MTBF]/4

I tried to create another calculated field [Rounded Quarterly Failure Rate] with IF [Quarterly Predicted Failure Rate] < 1 THEN [Quarterly Predicted Failure Rate] + 1 ELSE [Quarterly Predicted Failure Rate] END but it is not working. The number returned is rather large and doesnt relate to anything as far as I can tell. I also tried changing the field to an attribute but still not working

I am using MySQL and using tableau extracts

I attached a twbx of the chart but if the real twb is needed let me know. it uses 3 tables and is a bit confusing

Thanks

• ###### 1. Re: how to round up to 1 from anything less than 1?

Is [Quarterly Predicted Failure Rate] correct?

• ###### 2. Re: how to round up to 1 from anything less than 1?

Yes the calculation result is accurate. The problem is with the .48 result rounding down to 0

• ###### 3. Re: how to round up to 1 from anything less than 1?

Are you sure, because [Number of Records] without a sum() will always be 1 assuming you are using Tableau's automatically created Number of Records. In other words you are doing this calculation on every row. If not, then ignore that.

Perhaps you could share a screenshot of the current result?

If all you want is 1 when less than 1:

IF [Quarterly Predicted Failure Rate] < 1 THEN  1 ELSE [Quarterly Predicted Failure Rate] END

• ###### 4. Re: how to round up to 1 from anything less than 1?

I am pretty sure the calculation is correct. At least it is returning the correct result

for example:

[Number of Records]/3*8760/[MTBF]/4

4495/3*8760/70000/4 = 46.87 which is the same as the result of the calculation in tableau.

I tired what you suggested but it doesnt work. My [Rounded Quarterly Failure Rate] is now returning the actual number of records for each model number. i.e 4495.

I have attached the workbook so maybe it makes more sense.

• ###### 5. Re: how to round up to 1 from anything less than 1?

Take a look at Sheet 2 - is this what you expect - all of them are below 1? For every row of your data you are calculating the quarterly predicted failure rate. It looks like you have duplicated data as well. Does it then make sense to sum those (as you are doing in Sheet 1)?

• ###### 6. Re: how to round up to 1 from anything less than 1?

Thanks for digging into this Alex. Good catch on the duplicated data. Got that fixed.

I see that the SUM is actually what is giving the correct results on sheet 1 and I guess that is why my simple +1 calculation doesnt work.I think the issue I am having is that I need to SUM the number of records of each model and then divide by the non SUM of MTBF. I cant figure out how to do that. It always says I cant mix aggregate and non-aggregate arguments in a function.

Any other ideas?

Thanks

• ###### 7. Re: how to round up to 1 from anything less than 1?

Think i figured it out

[Quarterly Predicted Failure Rate] = SUM([Number of Records])/3*8760/MAX([MTBF])/4

[Rounded Quarterly Failure Rate] = IF [Quarterly Predicted Failure Rate] < 1 THEN 1 ELSE [Quarterly Predicted Failure Rate] END

Then I just set the number format to 0 decimals

Thanks for taking a look Alex

• ###### 8. Re: how to round up to 1 from anything less than 1?

No problem - I had a suspicion that's where we were headed...