Sum of Averages in Spotfire
Hello experts,
I thought it would be very easy, but I've been struggling for days to make this simple query work. What I need is an expression that gives me the sum of averages.
(Due to filtering requirements I can't pivot my data table or use calculated columns, it has to be an expression).
| Category | Item | Value |
| cat1 | it1 | 10 |
| cat1 | it2 | 20 |
| cat2 | it1 | 30 |
| cat2 | it2 | 40 |
| cat2 | it2 | 50 |
| Sum of Averages: | 55 |
So Avg(cat1) + Avg(cat2) = 55 is what I'm after, but no matter what I try I just can't make it work. Any help would be extremely appreciated!
(2) Answers
Thank you for your reply Sean,
I think I went overboard trying to simplify my query, my apologies for that. Each sum of averages would be a bar on a bar chart.
Using the table below, the X axis is <[Group] NEST [Category]>.
The Y axis should be the sum of average amounts per item.
So I should get 4 bars: 55 for Group1/Cat1; 155 for Group1/Cat2; 255 for Group2/Cat1; 355 for Group2/Cat2.
I hope it makes sense, the attached excel file shows clearly what I'm trying to achieve.
This thing's been driving me crazy for over a week now - your help is truly appreciated!
| Group | Category | Item | Amount |
| Group 1 | Category 1 | Item 1 | 10 |
| Group 1 | Category 1 | Item 1 | 20 |
| Group 1 | Category 1 | Item 2 | 30 |
| Group 1 | Category 1 | Item 2 | 40 |
| Group 1 | Category 1 | Item 2 | 50 |
| Group 1 | Category 2 | Item 1 | 60 |
| Group 1 | Category 2 | Item 1 | 70 |
| Group 1 | Category 2 | Item 2 | 80 |
| Group 1 | Category 2 | Item 2 | 90 |
| Group 1 | Category 2 | Item 2 | 100 |
| Group 2 | Category 1 | Item 1 | 110 |
| Group 2 | Category 1 | Item 1 | 120 |
| Group 2 | Category 1 | Item 2 | 130 |
| Group 2 | Category 1 | Item 2 | 140 |
| Group 2 | Category 1 | Item 2 | 150 |
| Group 2 | Category 2 | Item 1 | 160 |
| Group 2 | Category 2 | Item 1 | 170 |
| Group 2 | Category 2 | Item 2 | 180 |
| Group 2 | Category 2 | Item 2 | 190 |
| Group 2 | Category 2 | Item 2 | 200 |
| Attachment | Size |
|---|---|
 sum_of_averages.xlsx | 10.69 KB |
The format of your desired visualization will effect the exact implementation. But here is how you do nested aggregations in general - with the THEN keword:
Assuming you have your data table above, the following axes expressions would give you that 55 (Note: your [Value] column is renamed to [myValue] to avoid confusion with the built in [Value] keyword entitity used with the THEN expression):
- X-axis: %lt;[Item]%gt;
- Y-axis: Avg([myValue]), Avg([myValue]) THEN Sum([Value]) OVER (All([Axis.X]))
- Colr by: (Column Names)
1 Comment
Sean, when using the formula you shared, I get 90/205 for Item 1 and 115/205 for Item 2. I wasn't able to get the Group or Category breakdown that was suppossed to yield the 4 bars (55 for Group1/Cat1; 155 for Group1/Cat2; 255 for Group2/Cat1; 355 for Group2/Cat2).
Am I missing something? Thanks!
1 Comment
Sean, when using the formula you shared, I get 90/205 for Item 1 and 115/205 for Item 2. I wasn't able to get the Group or Category breakdown that was suppossed to yield the 4 bars (55 for Group1/Cat1; 155 for Group1/Cat2; 255 for Group2/Cat1; 355 for Group2/Cat2).
Am I missing something? Thanks!
1 Comment