Skip to main content

We have created a GI and we are calculating values in the Results Grid to come up with a Percentage Complete.  Formula is (ActualAmount / RevisedAmount)*100.   This should give us a Percentage Result.

However,  The Calculation results are inconsistent.

For Example, the Actual Amount is 2400 and the Revised Amount is 4800.  The Percentage Result is coming back at 100.  It should be 50%.

Another Example, The Actual Amount is 52716 and the Revised Amount is 138000.  The percentage Result is coming back as 76%  it should be 38%.

But then in another line example the calculation is correct.  The Actual amount is 7992 and the Revised Amount is 14800 and the resulting percentage is 54% and this is correct.

We can not figure out why this calculation is incorrect and inconsistent.

here is the actual line in the results grid.

=CInt(IIF(CBudgetRevenue.CuryRevisedAmount]=0,0  , (=BudgetRevenue.CuryActualAmount])/lBudgetRevenue.CuryRevisedAmount]*100))

Try

=CInt(IIF([BudgetRevenue.CuryRevisedAmount]=0, 0, CDec([BudgetRevenue.CuryActualAmount])/[BudgetRevenue.CuryRevisedAmount]*100))

 

This seems to have cleaned up most of the data issues, except for the first example 52716/138000  should be 38% but result is 76% & the 2400/4800 is still showing 100%.   However,  in many other lines, this did fix the calculation issues.    What does the CDec part do?

As you can see from the data below,  with the changes recommended by Daryl, most of the calculations are correct.  However, Line number 1 is incorrect (highlighted).   The calculation is Project Actual Amount / Project Budget amount.  This should be 38%   I have no clue how it is getting to 76% in this example.

This is formula in the results grid.   Revised Amount = Project Actual Amount (the caption was changed to make more sense to the users)

=CInt(IIF(CBudgetRevenue.CuryRevisedAmount]=0, 0, CDec( BudgetRevenue.CuryActualAmount])/ABudgetRevenue.CuryRevisedAmount]*100))

 

 

I can’t explain why one row would be behaving differently. I did notice that 38.2 (which is the correct result) is half of 76.4 (which I’m assuming is the rounded result you’re seeing.

 

CDec converts the amount to a decimal. The reason this works is because in code, dividing an integer by an integer will always result in an integer. Converting one to a decimal will result in the answer being a decimal, therefore not rounded prematurely.

Thanks for the help!  I appreciate it.

I encountered a very odd scenario today where CDec() was rounding a converted string to the nearest integer. CDbl() was working fine and is enough precision for our needs. In my case, I was curious if there was an issue with commas or decimal points.

The only way I can manufacture your results is by performing the operation twice with comma splits, but I think it’s worth noting that the row below, which also has an extra digit compared to other rows, is a factor of two too small.
My only takeaway has been to use CDbl() instead of CDec(), but I haven’t been able to find anyone else with the same issue.
I hope no one else stumbles across bugs like this, but just in case!

-Aaron

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved