How to sum data by in a category in Spotfire

Question

What would the custom expression be to sum data by a category, for each site.

Using the data below, I would like to Sum[X] for only values with category blue, for each site

What I have so far is Sum([X]) OVER [Site] --> Where / how do I put in the category qualifier?

Answer 1

the Intersect() function is a perfect fit here. it creates a hierarchy based on however many columns you list. more info in the documentation.

anyway, try the following:

Sum([X]) OVER (Intersect([Site], [Category]))

---

To do the same for only a single category, you can use an expression like

Sum(If([Category]="Blue",[X],0)) OVER ([Site])

This will leave a null/empty value when [X] is not "Blue" (case sensitive so beware!).

If you have multiple values, you can replace the condition with

If([X] in ("Blue", "Nurple", "Taupe"), ...)

Answer 2

what I found works best is: Sum(If([Category]="Blue",[X],0)) OVER ([Site])