Calculate average over only some rows while using OVER function

Hello, 

Here's an example of the data I have:

Example data (example_data)
GROUP VALUE CITY
Low 45 Fresno
Medium 56 Fresno
Low 34 Fresno
High 78 Fresno
High 88 Chicago
Low 50 Chicago
Medium 30 Chicago

I want to

Include a calculated column with the combined average of "Value" of the rows corresponding to Groups, "Low" and "Medium" By City
i.e. For Fresno: (45 + 56 + 34) / 3 = 45
For Chicago: (50 + 30)/2 =  40

If example_data were a data.table in R, I would do the following expression:
example_data[GROUP %in% c("Low", "Medium"), mean(VALUE, na.rm = TRUE), by = "CITY"]

How do you do this inside Spotfire? I know about the OVER expression:
Avg([Value]) OVER ([CITY])
But this doesn't let me calculate the average for only the rows corresponding to the "Low" and "Medium" groups.

(1) Answer

Login