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

book

Article ID: KB0081896

calendar_today

Updated On:

Products	Versions
Spotfire Analyst	All

Description

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

Issue/Introduction

Flagging a column if it has changed in daily data set.

Environment

Product: TIBCO Spotfire (desktop client / Analyst / Professional) Version: All Supported Versions OS: All Supported Operating Systems

Resolution

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

Additional Information

Custom Expressions

Feedback

thumb_up Yes

thumb_down No

Welcome to "KB Articles"