Hi,
i need to calculate the average of opportunities related to a single project, considering only the opportunities of a certain type ("slave_opportunity").
I've tried with different versions of the following formula with no luck...
ifElse(equal($project_opportunities_1,"opportunity_type","slave_opportunity"),rollupAve(related($project_opportunities_1,"amount"),0)
anybody can help? thank you
Hi marco baracco ,
The ifElse() formula cannot be used in this fashion. It is not designed to iterate through each record in the relationship. Instead, it would choose an arbitrary record in the relationship and execute the following statements depending on whether that one record satisfied the condition. Instead, you would want a formula like the following:
divide(
rollupConditionalSum(
$project_opportunities_1,
"amount",
"opportunity_type",
createList(
"slave_opportunity"
)
),
countConditional(
$project_opportunities_1,
"opportunity_type",
createList(
"slave_opportunity"
)
)
)
The formula conditionally adds the amounts of the opportunities where they have the value 'slave_opportunity' and then divides by the number of opportunities meeting that same condition to produce the average.
Chris
Hi marco baracco ,
The ifElse() formula cannot be used in this fashion. It is not designed to iterate through each record in the relationship. Instead, it would choose an arbitrary record in the relationship and execute the following statements depending on whether that one record satisfied the condition. Instead, you would want a formula like the following:
divide(
rollupConditionalSum(
$project_opportunities_1,
"amount",
"opportunity_type",
createList(
"slave_opportunity"
)
),
countConditional(
$project_opportunities_1,
"opportunity_type",
createList(
"slave_opportunity"
)
)
)
The formula conditionally adds the amounts of the opportunities where they have the value 'slave_opportunity' and then divides by the number of opportunities meeting that same condition to produce the average.
Chris
