Solved

formula to average GI results grid/data field columns

  • 29 February 2024
  • 13 replies
  • 88 views
S
Rank
Userlevel 5
Badge

All,

I’ve created a basic Vendor Score GI based on 6 user defined fields of price, responsiveness, quality, etc. I would like to have. a final visible column that averages the user defined fields for an average score.

I have something like this:

=[VendorR.AttributeVSCOREDELV]+[VendorR.AttributeVSCOREPRIC]+[VendorR.AttributeVSCOREQUAL]+[VendorR.AttributeVSCORERESP]+[VendorR.AttributeVSCORETAT]+[VendorR.AttributeVSCOREVARI]/6

I actually get a value calculated, but it’s not right. :D

I tried changing it to =sum(1+2+3/3) etc but it throws an error of 

“Cannot perform an aggregate function on an expression containing an aggregate or a subquery.”

Help, I’m lost lol!
 






 

icon

Best answer by darylbowman 1 March 2024, 21:42

View original

13 replies

S
Rank
Userlevel 5
Badge

I think I know the issue -- the values in the combo are 1-5 but are being treated as char/text and not ints. Is there a way to convert these to integers and then sum and average them?

darylbowman
Rank
Badge +11

Sum() is an aggregate function, which is why you’re getting that error. Try this:

=(CInt([VendorR.AttributeVSCOREDELV])+CInt([VendorR.AttributeVSCOREPRIC])+CInt([VendorR.AttributeVSCOREQUAL])+CInt([VendorR.AttributeVSCORERESP])+CInt([VendorR.AttributeVSCORETAT])+CInt([VendorR.AttributeVSCOREVARI]))/6

 

Notice that the additions are combined with () around them to indicate the addition should happen before the division. This is important as well.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
S
Rank
Userlevel 5
Badge

Sum() is an aggregate function, which is why you’re getting that error. Try this:

=(CInt([VendorR.AttributeVSCOREDELV])+CInt([VendorR.AttributeVSCOREPRIC])+CInt([VendorR.AttributeVSCOREQUAL])+CInt([VendorR.AttributeVSCORERESP])+CInt([VendorR.AttributeVSCORETAT])+CInt([VendorR.AttributeVSCOREVARI]))/6

 

Notice that the additions are combined with () around them to indicate the addition should happen before the division. This is important as well.

Ahhh, makes sense! But I get an error 406 when I save… need to look at this deeper.

darylbowman
Rank
Badge +11

You could try without converting to int, but make sure the block of additions is enclosed in parenthesis to force that to calculate before dividing.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
S
Rank
Userlevel 5
Badge

You could try without converting to int, but make sure the block of additions is enclosed in parenthesis to force that to calculate before dividing.

Yeah removing the Cint eliminated the 406 but it’s still not averaging correctly. Dang it.

darylbowman
Rank
Badge +11

If you remove the division, does it sum properly?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
S
Rank
Userlevel 5
Badge

If you remove the division, does it sum properly?

Ahh, didn’t think to try that. No, doesn’t sum… it just concatenates the chars from the score:

 

 

darylbowman
Rank
Badge +11

Try this: 

=(CInt(CStr([VendorR.AttributeVSCOREDELV]))

If that works, try this:

=CInt(CStr([VendorR.AttributeVSCOREDELV]))+CInt(CStr([VendorR.AttributeVSCOREPRIC]))

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
S
Rank
Userlevel 5
Badge

The first one threw a 406. :/

S
Rank
Userlevel 5
Badge

I’m wondering if there’s a way… it’s trying to ‘math’ the description, when the value id (I’m guessing) is an actual int. So if there’s a way for me to display the attibute value id and not the description it may sum and average properly.

 

 

darylbowman
Rank
Badge +11

The Value ID is a string also. In my testing, I can convert a combo attribute to an int, although it converts the Value ID, not the Description.

=CInt([Customer.COMPSIZE_Attributes])+1

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
S
Rank
Userlevel 5
Badge

The Value ID is a string also. In my testing, I can convert a combo attribute to an int, although it converts the Value ID, not the Description.

=CInt([Customer.COMPSIZE_Attributes])+1

Dude it worked!

This test you had me do had an extra leading paren (

=(CInt(CStr([VendorR.AttributeVSCOREDELV]))

When I removed that, I did this:

=CInt(CStr([VendorR.AttributeVSCOREDELV]))+10

And got this:

 

S
Rank
Userlevel 5
Badge

The Value ID is a string also. In my testing, I can convert a combo attribute to an int, although it converts the Value ID, not the Description.

=CInt([Customer.COMPSIZE_Attributes])+1

Dude it worked!

This test you had me do had an extra leading paren (

=(CInt(CStr([VendorR.AttributeVSCOREDELV]))

When I removed that, I did this:

=CInt(CStr([VendorR.AttributeVSCOREDELV]))+10

And got this:

 

The error comes in when wrapping the () to get it to divide /6 properly. Now I think I can just cheat, make a total score column that is hidden, and then just divide that hidden field by 6 for a visible Avg Score

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