How to sumifs based on multiple criteria on spotfire

[Valerine Tng]

Hi,

Let say I have this table, I want to calculate the profit in each period (i.e Revenue- Expense = Profit for period 1, 2, 3)

How do I use custom expression to achieve that

 

 

 

Description

Period

Activity

 

 

Revenue

1

100

 

 

Expense

1

50

 

 

 

 

 

 

 

Revenue

2

200

 

 

Expense

2

100

 

 

 

 

 

 

 

Revenue

3

300

 

 

Expense

3

200

[Fabian Duerr]

Option 1: Get the Profit in the same table

Here you could use this calculated column:

First(If([Description]="Revenue",[Activity],NULL)) OVER ([Period]) - First(If([Description]="Expense",[Activity],NULL)) OVER ([Period])

Option 2: Get your data to the right shape

Create a new data table with a Pivot operation and then simply subtract the columns:

[Revenue] - [Expense]