Beast Mode: Syntax issues on filtering out a Sum Total

markb

Hi - I am trying to remove certain IDs from a column then sum their values but can't get the syntax correct. Here is what I've gotten to but doesn't work. I can't add a filter on the card since the removed values need to be in another part on the chart.

CASE
SUM('values') - SUM(WHEN id='LIABILITY' THEN 'values')
END

MarkSnodgrass

Try this:

SUM(values) - SUM(CASE WHEN Id= 'Liability' THEN values ELSE 0 END)

MarkSnodgrass

If you don't want ID='Liability' in your card, the easiest way is to drag ID into the filters section and choose Not In and select Liability.

If you need those values for other parts of your card, but don't want it for this specific value, you could try this beast mode as another option.

SUM(CASE WHEN Id<> 'Liability' THEN values END)

This would sum everything but ID='Liability'

MarkSnodgrass

Try this:

SUM(values) - SUM(CASE WHEN Id= 'Liability' THEN values ELSE 0 END)

markb

Thank you! This cleared the syntax issue but realized it didn't get the value I'm comparing against and gave me negatives.

How would I structure this in order to simply remove the ID='Liability' from the sum('value') so they aren't included?

MarkSnodgrass

If you don't want ID='Liability' in your card, the easiest way is to drag ID into the filters section and choose Not In and select Liability.

If you need those values for other parts of your card, but don't want it for this specific value, you could try this beast mode as another option.

SUM(CASE WHEN Id<> 'Liability' THEN values END)

This would sum everything but ID='Liability'