Week number correct

[Francisco Aspillaga]

Hi Community,

I am creating a graph with the date on the X axis, but when I change to put the week number instead of the date it gives me two more week numbers. Ex. Today Nov 14 is week 46, and it shows me that it was week 48.

The formula used is:

BinByDateTime([DATE START PLANTED],"Week",0) as [Week]

In the image week 47 is actually week 45.

Can any correction be made so that the correct week number is displayed?

Thank you,

[Fredrik Rosell]

Hello Francisco,

In Spotfire 12.2, support for ISOWeeks (and Years) was added in Spotfire - see "Format Dates using ISO Weeks & Years Standard" (https://community.spotfire.com/s/article/What-s-New-in-Spotfire-12-2) - and that's what I would recommend using.

You could then e.g. tweak your expression to use ISO Weeks instead: <BinByDateTime([DATE START PLANTED],"isoweek",0)>

Example:

 

Copied from the help for the BinByDateTime function:

Creates a binned column based on a natural date, datetime or time hierarchy.

The first argument is the Date, Time or DateTime column to bin. The second argument is the hierarchy level definition written in the form of a string containing the desired date parts, separated by dots (e.g., "Year.Quarter.Month"). The third argument is the pruning level which specifies the level of the hierarchy to display. 

Valid arguments for Arg2 are combinations of:

'year' or 'yy' - The year.

'quarter' or 'qq' - The quarter.

'month' or 'mm' - The month.

'day of year' or 'dy' - The day of year.

'day' or 'dd' - The day.

'week' or 'wk' - The week.

'day of week' or 'dw' - The day of week.

'hour' or 'hh' - The hour.

'minute' or 'mi' - The minute.

'second' or 'ss' - The second.

'millisecond' or 'ms' - The millisecond.

Alternatively, Arg2 can also be a combination of:

'isoyear' - The ISO 8601 week-numbering year.

'isoweek' - The ISO 8601 week number.

Examples:

BinByDateTime([Column],"Year.Quarter.Month.Day",2)

BinByDateTime([Column],"ISOYear.ISOWeek",1)

Upgrading to a 12.2+ version (we just released the new 14.0 LTS) and using ISO Week instead is what I recommend. On older versions, maybe explore the options in the Community article "TIBCO Spotfire® Tips & Tricks: Reusable, Simple & Easy ISO Week Calculation with Spotfire" ( https://community.spotfire.com/s/article/tibco-spotfirer-tips-tricks-reusable-simple-easy-iso-week-calculation-spotfire)