Clarity

Hi All,

The list of formulas in the Calculated Attribute Field is very limited.  I need to be able to perform a calculation that does a "count" of values greater than 0.  There is no "Count" function available.

Does anyone know how to translate this function into something that works in the Calculated Attribute Field?

(IFNULL(sum(cop_scope_status),0)+IFNULL(sum(cop_schedule_status),0)+IFNULL(sum(cop_cost_eft_status),0))/(count(cop_scope_status)+count(cop_schedule_status)+IFNULL(count(cop_cost_eft_status),0))

The only functions available are:

Absolute

Add

Average

DateDiff

Divide

Max

Min

Multiply

Percentage

Power

Round

Sqrt

Subtract

Sum

Trunc

All attributes are tied to an instance(including calculated attributes). Therefore, the value that a calculated attribute instance holds will depend only on that parent object instance(and its attributes). Its scope does not extend to read and calculate from other object instances.

A count() function acts on more than one object instance at a time, and hence not within the scope of a calculated attribute.

The easiest way to achieve this is to probably just write a simple Gel Script.

I finally found a way to get the "Count" to work by using a Mathematical equivalent.

To get an average, you need the count.  I was able to convert a value to a 1 or 0 by using (value/(Max(value,1))

The Max function avoids having the "Divide by Zero" error by putting a 1 in place of a 0.  0/1 = 0

1/1 = 1

If you just want to display a calculated value on the project edit view (does not work on list views), then this technique can do this ; TIP : How To Put Any Dynamically Generated Value On A Clarity Object 

Hi All,

I resolved it.  For the Count, I used a mathematical equivalent:

(Sum(cop_schedule_status,cop_scope_status,cop_cost_eft_status)/(Sum(cop_schedule_status/Max(cop_schedule_status,1),cop_scope_status/Max(cop_scope_status,1),cop_cost_eft_status/Max(cop_cost_eft_status,1))))

To get an average, you need the count.  I was able to convert a value to a 1 or 0 by using (value/(Max(value,1))

The Max function avoids having the "Divide by Zero" error by putting a 1 in place of a 0.  0/1 = 0

1/1 = 1