Need the Date of ordinal Weekdays

[Lynn Frieden]

Hello,

I need to develop a calculated column that will determine the date of the First, Second, Third Monday, Tuesday, Wednesday, etc. of each Month. Is there a way to do that in Spotfire

[Shuting Fu]

The first step is to get the first day of the month [First day of the month]:

Date(Year([MyDate]),Month([MyDate]),1)Based on this, the first Monday of the month can be calculated by:

DateAdd("day",6 - DayOfWeek(DateAdd("day",5,[First day of the month])),[First day of the month])Similarly, the second Monday is:

DateAdd("day",6 + 7 - DayOfWeek(DateAdd("day",5,[First day of the month])),[First day of the month])And the first Tuesday is:

DateAdd("day",6 - DayOfWeek(DateAdd("day",4,[First day of the month])),[First day of the month])

[Lynn Frieden]

I tested that as:Case when DateAdd("day",6 - DayOfWeek(DateAdd("day",5,[First day of the month])),[First day of the month]) then "First Monday"

when DateAdd("day",6 + 7 - DayOfWeek(DateAdd("day",5,[First day of the month])),[First day of the month]) then "Second Monday"

else "No" endI received an error "Invalid function call for 'If'"  Am I missing something

[Shuting Fu]

This won't work because the the condition set for the "when" statement doesn't exist yet. You can try with document properties though. It depends on your data table structure to determine the custom expression syntax.