CODEWITHSUNDEEP
spotfiretibcointersect
Daniel Leite
Daniel Leite

Reputation: 1

Spotfire on function - Create new calculated column with difference between different table rows

1

I would like to calculate a difference in value between a line that is from column D (end date) with the line below but wuth data form column C (start date).

Tried the following custom expression but was unsuccessful

MostCommon(\[Type\]) over (Intersect(\[END_Date\],\[START_Date\]))

I've also tried adding Type to the Intersect statement, e.g. Intersect(\[ID\],\[Type\], Previous(\[Date\]), but I get a similar result.

how to perform this calculation between different lines?

Upvotes: 0

Views: 606

Answers (1)

Gaia Paolini
Gaia Paolini

Reputation: 1492

I have some gaps in my understanding, so I will answer according to my assumptions.

I am guessing that you want this date difference to reset at every new [id]. I don't really understand the role of [tipo] as it seems to be different for each line, so I ignored it.

You don't say what this date difference should look like, so I calculated it in days (but it can be changed to whatever format you prefer).

There is a Lag function in Spotfire, but it does not work with groups. So I calculated the lag in two steps:

first create a [rank] variable to assign a running index within each [id] as:

DenseRank([data termino],[id])

then create a lag end date [lag termino] (i.e. the end date of the previous row in the group) as:

Max([data termino]) over (Previous([rank]))

then create the date difference between the [lag termino] and the start date [data inicio]:

SN(DateDiff('day',[lag termino],[data inicio]),0)

the SN() function assigns 0 to the first row in each [id] group, where the date difference would be undefined.

You can combine the last two steps as (skip the definition of [lag termino]):

SN(DateDiff('day',Max([data termino]) over (Previous([rank])),[data inicio]),0)

But since 'over' does not accept expressions after it, you would need a separate column for [rank] anyway.

Upvotes: 1

Related Questions