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?

niko · Answer 1 · 2016-04-19T18:42:50.657

2

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"), ...)

edited Apr 19 '16 at 18:42

answered Apr 18 '16 at 23:54

niko

I see, but how would I specify only the Blue category though? I do not want to sum any data that is from the Red category. Thanks! – NightLearner Apr 19 '16 at 12:39
Ah ok I misunderstood. I'll update the answer in a minute. – niko Apr 19 '16 at 12:41
1

Niko, I had to modify your expression because it wasn't going through, turns out its a variation of what you suggested and what I found works best is: Sum(If([Category]="Blue",[X],0)) OVER ([Site]) Thanks for the initial input though, got me most of the way! – NightLearner Apr 19 '16 at 18:40
that's correct. sorry; posted via mobile and before my coffee! glad you got it solved :) – niko Apr 19 '16 at 18:41

score 0 · Accepted Answer · answered Apr 19 '16 at 18:42

0

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

answered Apr 19 '16 at 18:42

NightLearner

2 Answers2