How to flag changes in column values if they have changed over a particular day.

Created
October 08, 2021 23:26

Date Posted: 2018-05-18
Product: TIBCO Spotfire®

Problem:

Solution:

Let's take an example of a daily stocks dataset with two columns "DATE" & "Stock".The stock price is prone to change multiple times in a day. In this article, we will take a look at how we can flag these changes in the "Stock" value for a particular day.

Here is a sample dataset on how this 'Flag' should work.

Date	Stock	Flag1
1/1/2016	10
1/1/2016	11	Change
1/1/2016	11	NoChange
1/1/2016	10	Change
1/1/2016	12	Change
1/1/2016	12	NoChange
1/2/2016	12
1/2/2016	10	Change
1/2/2016	10	NoChange
1/2/2016	11	Change

To flag the data changes in the "Stock" column for any particular day, we will make use of calculated columns.

Let's create three calculated columns:

"ID" which calculates the index of the rows.
"FlagColumn" which checks if the previous value of the column "Stock" is same as current value of the column "Stock". If it is this column will display the value of "FlagColumn" as "nochange",else we display as "change".
"Flag1" checks if the Date at the previous row is equal to the Date at the current row. If it is equal it will display the value of calculated column "FlagColumn", else it will display "Null".

Here are the expressions needed for each of the above calculated columns:

Column "ID" : RowId()
Column "FlagColumn" : case when First([Stock]) OVER (Previous([ID]))=First([Stock]) OVER ([ID]) then "nochange" else "change" end
Column "Flag1": case when First([DATE]) OVER (Previous([ID]))!=First([DATE]) OVER ([ID]) then "" else [FlagColumn] end

Custom Expressions