I can't figure out how to filter for a Choice field to get a sum

Ask for Help
1

I have a table that has 2 fields I need to use to calculate a sum, MR and Setup.

MR is an integer field
Setup is a Choice List field

I can calculate the number of occurrences containing ‘DD’ using this formula

count = sum(
'DD' in setup for trade in $group 
for setup in trade.Setup 

if trade.MRTrade == True
)

return count

image
image1121×896 101 KB

I can calculate a filtered sum of the MR field using this formula. Stack is a Choice filed (not Choice List). This is an example, I don’t want to filter on this field.

sum(trade.MR for trade in $group

if trade.Stack == 'LL'

)

image
image1296×433 75.2 KB

But when I try to filter the Setup list field and sum MR, this formula gets me 0.

sum(trade.MR for trade in $group 

if 'DD' in trade.Setup

)

image
image1502×837 155 KB

How do I filter the Choice List and get a sum of the MR field?

thanks

2

Combining what you did in the first two formulas:

sum(
  trade.MR
  for trade in $group
  for setup in trade.Setup
  if 'DD' in setup
)

Because Setup is a list, and ‘DD’ is only a substring (never a complete list element) you have to loop over the list and check 'DD' in setup for each one.

Your first formula is equivalent to:

sum(
  1
  for trade in $group
  for setup in trade.Setup
  if 'DD' in setup
  if trade.MRTrade
)

The reason the first formula works is that True and False are equivalent to 1 and 0 and can just be added together.

1 Like