Calculate a count using 2 fields

Question & Answer
1

This should be simple but I can’t figure out which function to use and the what is should look like.
I have 2 columns, country and action (CAN/USA and Order/Cancel/Return). I want to calculate how many Canadian Orders have been placed. I can see it in a pivot table but need the number all by itself to use in another calculation.
I tried ifelse({COUNTRY} = ‘CAN’, countif({Action},{Action} = ‘ORDER’),0) and I’m getting aggregate/non-aggregate errors

If I replaced countIf with countOver I get: Expression countOver({Action},{Action} = ‘ORDER’) for function countOver has incorrect argument type countOver(String, ). Function syntax expects Any, List, Calculation Level
Any, List
‘countOver()’.

Thanks

2

Hello @Stapper, here is something you can try:

ifelse({COUNTRY} = 'CAN' AND {Action} = 'ORDER', 1, 0)

This is a trick I will use a lot to do counts when I am handling ifelse statements. You can use a sum on that calculated field to get the desired number for your other calculation. Let me know if that works!

3

@DylanM , Yes! That was the trick I needed and I’m sure I will be using this regularly. And when added to my other calculation I was relieved not to receive any errors. Bonus points for helping me understanding the ifelse function. Greatly appreciate your help.

1 Like
4

Hello @Stapper, that is definitely a good trick to hold on to. I’m glad it resolved your issue!