Sum of unique values in a graphical table

[Mickael Mar]

Hello dear spotfire experts,

I have the following issue that I can't solve.

I have repetitive values in a data table, and I want to perform a kind of "unique sum" of those values.

For instance, my data look like this:

 

 

 

 

Group

Group Value

Item

Item Value

Year

 

 

A

30

X

1

2016

 

 

A

30

Y

2

2016

 

 

A

30

Z

3

2016

 

 

B

45

Y

4

2016

 

 

B

45

Z

5

2016

 

 

A

60

J

9

2017

 

 

A

60

K

8

2017

 

 

B

25

J

7

2017

 

 

B

25

K

6

2017

 

 

B

25

Z

5

2017

 

 

 

And I would like something like this:

 

 

 

Year

Group Values

Item Values

...

 

 

2016

75

15

...

 

 

2017

85

35

...

 

 

 

but I'm stuck on the calculation of the "Group values" column.

I tried something like First([Group Value)] Over ([Group]) Then Sum([Value]) but it is not valid.

Any help would be much appreciated.

Mickael

[Sean Riley]

Please see this KB which describes exactly how to do that:

 

 

Identifying duplicate rows using custom expressions.

https://support.tibco.com/s/article/ka11a000000HohHAAS/Tibco-KnowledgeAr...

 

 

Example:

 

 

Sum(if(Rank(baserowid(),"asc",[myGroup])=1,[Value],Null))

[Mickael Mar]

Thank you Sean.

I really thought it was possible using some OVER and THEN... Anyway, it works fine!

Many thanks.

[mike lee 2]

Hi, This kinda worked for me:

 

 Avg(if(Rank(baserowid(),"asc",[myGroup1])=1,[Value],Null))

 

However, only if the chart (clicking on a group) is marked to a specific group.  If I unmark the data, then the values are all off.  

 

Pls see pictures I hope "picture is worth a thousands words"

 

 

The Marked value of 1879.50 is correct.  when unmarked, 1333.50 is incorrect!

 

Any ideas

 

 

 

Thanks in advanced.