-
1. Re: how to round up to 1 from anything less than 1?
Alex Kerin Nov 1, 2012 2:35 PM (in response to Cody DeNiro)Is [Quarterly Predicted Failure Rate] correct?
-
2. Re: how to round up to 1 from anything less than 1?
Cody DeNiro Nov 1, 2012 2:46 PM (in response to Alex Kerin)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?
Alex Kerin Nov 1, 2012 7:15 PM (in response to Cody DeNiro)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?
Cody DeNiro Nov 2, 2012 6:59 AM (in response to Alex Kerin)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.
-
Book2.twbx.zip 979.1 KB
-
-
5. Re: how to round up to 1 from anything less than 1?
Alex Kerin Nov 2, 2012 7:34 AM (in response to Cody DeNiro)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)?
-
Book2 AK.twbx.zip 1,006.2 KB
-
-
6. Re: how to round up to 1 from anything less than 1?
Cody DeNiro Nov 2, 2012 10:20 AM (in response to Alex Kerin)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?
Cody DeNiro Nov 2, 2012 10:38 AM (in response to Cody DeNiro)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?
Alex Kerin Nov 2, 2012 12:45 PM (in response to Cody DeNiro)No problem - I had a suspicion that's where we were headed...