Hi there,
I’m trying to make a virtual column that’s a really simple division of two numbers
the first number is a count(filter(table1, condition)) formula which equals 19
the second number is count(table1) formula that equals 39
i.e. the first is a subset of the second, and I’m after the %
both of these work fine and show as they should. but when I divide the first by the second, I get 0.00, regardless of whether I set the column type as a number, a decimal, percent…
…help?
@Olivia_Bryanne_Zank1
Try with this:
([First Number] / [Second Number]) * 1.00
thanks - still says 0.00
@Olivia_Bryanne_Zank1
Can you try with:
([First Number] * 1.00) / [Second Number]
this worked! thanks a lot
You’re welcome
@LeventK, why must one do the *1.00? Surely dividing the two numbers should work??
A Number value is an integer, a number with no fractional component.
When two Number values are divided, the result will also be a Number value, resulting in the loss of the fractional component that may result from the division. For instance, 5 / 2 gives 2 instead of 2.5.
If one or both of the values in the division operation are Decimal values (which have fractional components), the result will be a Decimal value.
Multiplying a Number value by a Decimal value produces a Decimal value, so multiplying by 1.0 is an easy way to convert a Number to a Decimal.
@Steve. Thank you for the explantion. So if the result is in a Column that is set as a Decimal then why do we still have to multiply by 1. 00?
@Henry_Scott
If your columns are explicitly set to Decimal then you don’t need to do that multiplication for sure.
The result column type doesn’t come into play until after the fractional component has been lost. 5 / 2 gives 2, not 2.5. Even if you save the result to a Decimal column, you’re still saving 2 (which becomes 2.0), not 2.5. You have to indicate you want Decimal values used sooner, before the fractional components are lost.
As @LeventK notes, you don’t need to worry about these conversions if all of the numeric values involved come from Decimal columns rather than Number columns, and the literal numeric values are expressed as real numbers rather than whole numbers (e.g., 2.0 rather than 2)
Ok noted. So not just target/result column must be decimal, but your Number columns used in the calc must be changed to Decimal as well
Or their values converted to Decimal using the multiply-by-1.0 trick. There’s also a DECIMAL() function that does the same thing.
Thanks Steve
Hi! I tried the above solution but I am still getting 0 when dividing anything. DO you have any other suggestions?