Parsing Dates and calculating Last day of month with a Custom Expression

I have two columns with string dates called Start month and end Month as "yyyy-mm" but I need to convert them into a Date data type. for that, I add a Transformation or Calculated column:

The Start month is easy to parse:

Date(Integer(left([Start Month],4)),Integer(right([Start Month],2)),1)

For the end month, I have to add a month and extract one day after I parse the date:

DateAdd('day',-1,DateAdd('month',1,Date(Integer(left([End Month],4)),Integer(right([End Month],2)),1)))

2 comments:

Tushar R said...

Very helpful post.
I'm new to Spotfire and wanted to know if there is anyway to capture the min and max date from a date filter range. For e.g. we have data from Jan 01 to Dec 31. We also create a date slider and select data from range Mar 30 to Jun 30. Is there any script or any feature that can retrieve values "Mar 30" and "Jun 30".

Leviaguirre said...

I don't know of a way to *get* the min and max date from the date filter directly, except to *set* those values. I would get the min and max values from the filtered rows:

#get list of filtered rows
dt = Document.ActiveDataTableReference
rows = Document.ActiveFilteringSelectionReference.GetSelection(dt).AsIndexSet()

#get min and max values from the set
min = dt.Columns["Date"].RowValues.GetFormattedValue(rows.First)
max = dt.Columns["Date"].RowValues.GetFormattedValue(rows.Last)