Product: TIBCO Spotfire®
How to show the first day of each week on the x-axis of a chart
In normal use, the Week() function returns the week for a date as an integer between 1 and 54, where the selection of the days identified to be in the first week of the year depends upon regional settings. Using the result from this function on the x-axis of a chart may make it hard to understand which days are included in each of a year's numbered weeks. This article explains how to show the Monday of each week for a given date.
For a given date column "myDate", you can use the following custom expression on a visualization axis or calculated column to find the Monday of week for the given date. This uses the function “DayOfWeek” to find which day it is, then adds or subtracts the needed days to find the Monday.
Note: Regional settings determine the start of week as well as the formatted output of the new column. This example assumes the start of the week is "Sunday". If the regional settings have a different start of the week then the expression will need to be adjusted accordingly.
case DayOfWeek([myDate]) when 0 then DateAdd("dd",1,[myDate]) when 1 then [myDate] when 2 then DateAdd("dd",-1,[myDate]) when 3 then DateAdd("dd",-2,[myDate]) when 4 then DateAdd("dd",-3,[myDate]) when 5 then DateAdd("dd",-4,[myDate]) else DateAdd("dd",-5,[myDate]) end
For example, the expression above was used to calculate the "Monday of Given Week" column here: