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

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.
 

DateStockFlag1
1/1/201610 
1/1/201611Change
1/1/201611NoChange
1/1/201610Change
1/1/201612Change
1/1/201612NoChange
1/2/201612 
1/2/201610Change
1/2/201610NoChange
1/2/201611Change

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

Issue/Introduction

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

Additional Information

Custom Expressions
Powered by Wolken Software