0

I just started using Spotfire and I am having a bit of a tough time figuring out how to create a particular calculated column, I need your help please.

Here's a sample of the data I'm working with and the calculated column (NewCol) with the results :

Table

Each Portfolio is related to another one "Attached_portfolio" except those with the value "O" in the "Porxy" column which are called portfolio model.

For all Portfolio with "N" in the column "Proxy" and for each "Label", all I want to do is to :

  • Sum the amount value (column "Amount") with the amount value of the attached portfolio in the NewCol.

For all Portfolio with "O" in the column "Proxy" and for each "Label", all I want to do is to :

  • Keep the same amount value in the NewCol

Thank you in advanced for your help.

user1816051
  • 1
  • 2

1 Answers1

0

enter image description here

Calc Col 2 is what you want. To get this to work, I needed an intermediate column (probably due to how Spotfire evaluates If/Case statements. The first formula is Sum([Amount]) OVER ([Label]). The second formula is case when [Proxy]="O" then [Amount] else [Calc Col] end

The first one sums all values over label. Than's how you end up with 800. It is 700 + 100. The second column just tells it when to use that vs the regular amount. Combining these two is when spotfire evaluated the "O" rows together, then when it sums over the Label, those are removed from the pool, so the sum doesn't work correctly.

Mark P.
  • 1,827
  • 16
  • 37