Simple Division Formula Not Working - [Fixed]

[1minManager]

This one had me totally baffled...  Posting here in case it helps someone else.

So I've got a CRM type app where we are simply trying to get the conversion ratio from sales lead to customer.  There are just 3 relevant columns, all virtual:

[Leads]

COUNT(Select(My Jobs[PriceID],TRUE))

[ Customers]

COUNT(Select(My Jobs[PriceID],OR(
   [Status]="Won",
   [Status]="Scheduled",
   [Status]="Completed",
   [Status]="Paid",
   [Status]="Reviewed"
   )
))

 [Conversion Ratio]

COUNT(Select(My Jobs[PriceID],OR(
   [Status]="Won",
   [Status]="Scheduled",
   [Status]="Completed",
   [Status]="Paid",
   [Status]="Reviewed"
   )
))
/
COUNT(Select(My Jobs[PriceID],TRUE))

The issue is that in a detail view  [Conversion Ratio] is always zero

Yet works fine in From view

The "My Jobs" slice is simply [PriceUser]=UserEmail() where [PriceUser] is simply an initial vaule of UserEmail().  So the the email of the app user who created it. 

Note that in the formula for [Conversion Ratio] I've been explict and not simply used [Customers]/[Leads] so that there are no dependancies between virtual columns which can sometimes cause detail and form view to be different.

Things I've tried (none of which worked, all just showed zero)

1. Created another virtual column 
2. Previewed the app as a different users
3. Used [Customers]/[Leads] instead
4. Rewrote the formula to remove the slice
5. Refreshed the browser tab and closed and reopened the tab
6. Changed the column name in case it was somehow reserved
7. Tried the colunm type as Percentage, Decimal, Number and Text
8. I even tried "1/2" which was still 0.00 !!!!!!!!!!!!

But then I tried "1.00/2.00" which worked and gave 0.50.  So after much further screaming and hair loss I've managed to fix the formula with...

DECIMAL(COUNT(Select(My Jobs[PriceID],OR(
   [Status]="Won",
   [Status]="Scheduled",
   [Status]="Completed",
   [Status]="Paid",
   [Status]="Reviewed"
   )
)))
/
DECIMAL(COUNT(Select(My Jobs[PriceID],TRUE)))

I can't help thinking though that, if I was new to Appsheet, I might have not bothered to spend over an hour trying all the above items.  When is Google going to fix the discrepencies between Form and Detail formulas and the issues around Decminal vs Numbers in calculations?

https://support.google.com/appsheet/answer/10107887?hl=en&sjid=18407104969037606424-EU

Simon, 1minManager.com

[Marc_Dillon]

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Fix-for-expression-giving-0-or-missing-decimal-p...

[1minManager]

Agreed, but its not very intuiative is it 🤣

[Marc_Dillon]

I suppose I would agree, it's not intuitive for laymen or beginners. But integer division is a pretty standard thing across many software systems and/or programming languages. I wouldn't call it something that needs fixed.

The discrepancy between the temporary result in a form view, and the final result everywhere else, I'd agree is certainly something that should be fixed.

[ProProjects]

Came across this same issue with my own CRM app trying to calculate conversion rate and it's true weird math issues. Read this and went back and changed my virtual columns doing the counting to type DECIMAL with 2 in the value for decimal places and my percentage conversion rate virtual column now works!!! Thank you.