TIBCO Spotfire - Insert Calculated Row

Question

I'm trying to insert rows to the table in Spotfire that are a sum of existing rows

Existing Table:

ID  SUB_ID  Line  Quarter  Value   
1   123     A     1        1  
1   123     A     2        2  
1   123     B     1        3  
1   123     B     2        4  
1   124     A     1        5  
1   124     A     2        6  
1   124     B     1        7  
1   124     B     2        8  
2   123     A     1        9  
2   123     A     2        1  
2   123     B     1        2  
2   123     B     2        3  
2   124     A     1        4  
2   124     A     2        5  
2   124     B     1        6  
2   124     B     2        7

Rows to Add:

ID  SUB_ID  Line  Quarter  Value (sum)
1   123     A     2        3  
1   123     B     2        7  
1   124     A     2        11  
1   124     B     2        15  
2   123     A     2        10  
2   123     B     2        5  
2   124     A     2        9  
2   124     B     2        13

What is the best way to do this?

What is the source of the table (information link, database, file)? Can you add a 2nd copy of it (by inserting rows) and pivot it? — Monte_fisto, May 19 '16 at 08:06
it's an information link. The data source is dynamic, so it would need to be based on an equation. Is there a way to change the SQL query in the Information Designer to do this? — user3783314, May 19 '16 at 14:56
Yes- you could create a 2nd copy of the information link with different SQL (by pressing the SQL button in the information designer). You could also perform the calculations you need when adding then information link using the transformations section of the add data tables wizard. — Monte_fisto, May 19 '16 at 15:20
That created new columns with the Sum(value) for each quarter. Is there something that will just add to the existing table and in the Quarter Column I can label these rows as 2Q Sum? — user3783314, May 19 '16 at 15:55

score 3 · Answer 1 · edited May 23 '17 at 12:22

3

the steps to implement the suggestion Monte_fisto made are outlined in another answer. make sure that you add the column as a transformation (Insert>Transformations...) rather than as a calculated column or you won't be able to use it as an index for the join.

another potential solution is to insert a calculated column with an expression like:

Sum([Value]) OVER Intersect([ID], [SUB_ID], [Line])

and then having your visualization measure that column.

it really depends on what you're going to do with your new data. if you provide some more detail I'll try to answer more specifically.

edited May 23 '17 at 12:22

Community

1
1

answered May 19 '16 at 17:34

niko

3,946
12
26

So eventually, I want to be able to create a cross tab ID, SUB_ID, and Line are Vertical Axes With the Quarter field as the horizontal – user3783314 May 19 '16 at 21:39

score 1 · Answer 2 · answered May 19 '16 at 18:53

1

You can also create a pivoted derived table if you want to preserve your original table. Otherwise, just add a pivoted tranformation (insert > transformation)

File > Add data table > Add > From current Analysis > your Table

answered May 19 '16 at 18:53

jleviaguirre

686
6
10

TIBCO Spotfire - Insert Calculated Row

2 Answers2