How to show the first day of each week on the x-axis of a chart

How to show the first day of each week on the x-axis of a chart

book

Article ID: KB0076564

calendar_today

Updated On:

Products Versions
Spotfire Analyst All Versions

Description

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.
 

Issue/Introduction

This article explains how to show the Monday of each week for a given date

Resolution

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:
User-added image