Product: TIBCO Spotfire®
How to flag changes in column values if they have changed over a particular day.
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.
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".
- 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