The formula seems to be correct but returns 0 values

[Aleksandra]

Hello,

I hope someone can help me with this one.  I need to calculate 3 ratios here: Success Rate, Cancelation Rate, and Failure Rate. To do so, I calculated values of New, Canceled, Completed, In Progress, and Trashed using SELECT COUNT and those values are correct. Then, I calculated the Total by summing them; that value is correct too.

(([@calcfield:3]+[@calcfield:1]+[@calcfield:2])/[@calcfield:6])*100 - This is the formula for Success Rate. It is (New + In Progress + Completed/ Total)*100

([@calcfield:4]/[@calcfield:6])*100 - This is the formula for Cancelation Rate. It is (Canceled/Total)*100

([@calcfield:5]/[@calcfield:6])*100 - This is the formula for Failure Rate. It is (Trashed/Total)*100.

Everything was working fine as, I had only In Progress, New and Completed projects in my Projects table. Here is the screenshot of it.

When I added the test project whose status was Canceled to test this, the rates were incorrect.

Does anyone have any idea why this could happen? When I added the test project which status was New everything was correct again. It looks like there is an issue with formulas when I add the Canceled or Trashed project only, but I cannot figure out why. Thank you all in advance for the support.

Regards,

Aleksandra

[CoopperBackpack]

Hello @Aleksandra,

The COUNT() function returns a value that has an Integer data type.

The Total is the sum of values returned by The COUNT() function, so Total is also an Integer.

In calculations when all the arguments are integers the result is an integer too.

For example: Success Rate in your Example#2 (3+ 1+ 4)/ 9 = 0.88, however only the integer part of the result is returned and it is 0.

To solve this one of the arguments should be converted to the Number (so, the decimal part is required).

For example, in Total you can change the formula: ( New + In Progress + Completed + Canceled + Trashed) *1.0

In this case ratios will be calculated correctly. If needed use the Formatting option to round the results, etc. 

https://howto.caspio.com/localizations/formatting-options/

[Aleksandra]

Hi @CoopperBackpack,

Thank you for your response. I had all of my values (including arguments) converted to numbers, but I put 0 for decimals there as they will always be round numbers (integers). I was thinking not having decimals might be an issue, so I put 2 decimal places for them as well, but it didn't work.

What worked is your advice to multiply the total value by 1.0.  I got the correct values now, so it is resolved.  Thank you so much.

Regards,

Aleksandra

 

[Tubby]

You can also multiply your values by 100.0 instead of 100 so the field detects the float/decimal and does not force convert to integer