Count based on multilple values

Hi Yves Bourgarel ,

To achieve this use case, I recommend doing the following:

1. Create a calculated field (e.g. case_evaluation_c) in the Cases module to evaluate the criteria you want to count records on. Based on your example, the formula could look like the following:
   

   ifElse(
       and(
           equal(
               $type,
               "reclamation"
           ),
           not(
               equal(
                   $status,
                   "closed"
               )
           )
       ),
       "Open - Reclamation",
       ""
   )

2. Then, in the custom field on your Contacts module, you would use the countConditional formula to count cases based on the value in your custom field from step 1. The formula would look like the following:
   

   countConditional(
       $cases,
       "case_evaluation_c",
       "Open - Reclamation"
   )

The reason the first field is necessary is that the countConditional formula can only accept a single condition. The first field allows you to evaluate as many conditions as you need within that module to then give you reliable datapoints for conditional rollup and count calculations in parent modules.

Chris

Hi Yves Bourgarel ,

To achieve this use case, I recommend doing the following:

1. Create a calculated field (e.g. case_evaluation_c) in the Cases module to evaluate the criteria you want to count records on. Based on your example, the formula could look like the following:
   

   ifElse(
       and(
           equal(
               $type,
               "reclamation"
           ),
           not(
               equal(
                   $status,
                   "closed"
               )
           )
       ),
       "Open - Reclamation",
       ""
   )

2. Then, in the custom field on your Contacts module, you would use the countConditional formula to count cases based on the value in your custom field from step 1. The formula would look like the following:
   

   countConditional(
       $cases,
       "case_evaluation_c",
       "Open - Reclamation"
   )

The reason the first field is necessary is that the countConditional formula can only accept a single condition. The first field allows you to evaluate as many conditions as you need within that module to then give you reliable datapoints for conditional rollup and count calculations in parent modules.

Chris

Nice one Chris Raffle 

Thank you Chris,

It does look great indeed.

However I was getting the error message "Attempted to evaluate expression with an invalid format : Open Reclamation". Which block entirely the Contact module. Thank god I was in sandbox ;)

So i did a couple of changes, starting by making both fields integer, then i inserted the following formulas : 

ifElse(
and(
equal(
$type,
"reclamation"
),
not(
equal(
$status,
"closed"
)
)
),
1,
0
)

and  :

countConditional(
$cases,
"case_evaluation_cv2",
1
)


Problem is  : the calculated field on the contact does not return the correct value.

I suspect there might a type problem with the second formula : apparently the second argument is supposed to a list. I tried using the array function (as indicated in the help) and [ ], they both caused an error.

What am i doing wrong ?

Thank you for your help