Empower not dealing with ABS( in CF

Dan_Ramlose

So, I have some CFs for CU AV calcs. Worked fine on another installation, not working here.

Basic problem is the ABS(M-AveragePercentClaim) portion. It keeps spitting out the AveragePercentClaim instead of a much smaller number. M is being calculated correctly and AveragePercentClaim is being calculated correctly.

I tried ABS(-1*M+AveragePercentClaim) - no help.
I've tried additional parentheses. No help. Empower just cannot add these terms correctly!
I even tried -1*M+AveragePercentClaim and I get -AveragePercentClaim instead of a small number (which should be negative, in this case as M>AveragePercentClaim).

Any suggestions?

Empower2018

Might be the alphabetical naming of the custom fields. CFs that depend on each other are processed first in the order of capitals (ZZZ is calculated before aaa) so your AveragePercentClaim is calculated first then M is calculated but what did you name the CF ABS(M-AveragePercentClaim)? Make sure is starts with a letter greater than M, say for example SubtractedValue or similar. Whenever Empower sees the function "Summarize Custom Fields" in a sample set, it goes back to the top of the sample set and looks for labels and fields to add up all your averages before spitting out a final result. I suspect while all this is going on, the custom field you have for ABS(M-AveragePercentClaim) is tapping its fingers impatiently waiting for a value for M which hasn't been calculated yet! 
I hope this works for you, good luck. 

Dan_Ramlose

I did not know that! I figured that there was just some Oracle side curse being applied to a couple of my fields. I did (eventually) get it to work when I rewrote the CF containing the bits posted above as USP_AV. 
Best answer I've seen in quite a while. Thanks lots!
I wonder if this is in the help or training docs I've got access to...

Empower2018

Im glad it worked. Yes, I learned this one the hard way. Essentially, if you are running custom fields that depend on each other and if one or more of them have a summary function like AVE, %RSD etc then they must be named in alphabetical order or else one wont have the calculation in time before the other kicks in. If you have a CF called A and of the type Amount*CConst1, a second CF called B of the type SAME.%..AVE(A) and a final one called C of the type B*CConst2 then they will all operate in order of A,B then C so that values are populated in sequence. The problem starts if your second custom field was called Z instead of B, then C would be waiting for a value that hasn't been calculated yet and hence return a blank or 0. 

Yes, this is outlined in Empower 3 help not sure the exact wording but most likely under "Intersample Summary Syntax Rules" or similar. 

Dan_Ramlose

So, where would numbers fit into this scheme?
Would CFs named aa, AA, zz, ZZ, 01, 02 be figured in the order:
AA, ZZ, aa, zz, 01, 02 or
01, 02, AA, ZZ, aa, zz?

Empower2018

I think your final designation is correct ie numbers before capitals before lower case. Not 100% on the numbers though.

HeatherLongden

I defer to others that know better, but I think you need 2 sets of parentheses. ABS(xx) needs one set around a SINGLE FIELD, but you had a (x-y) instead of a single field.
I think simply ABS((x-y)) might have worked?
Or maybe ABS can only work of single field so you needed to save that x-y value in the DB first