Is [Quarterly Predicted Failure Rate] correct?
Yes the calculation result is accurate. The problem is with the .48 result rounding down to 0
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
I am pretty sure the calculation is correct. At least it is returning the correct result
[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
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
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?
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
No problem - I had a suspicion that's where we were headed...