Jump to content
We've recently updated our Privacy Statement, available here. ×

Week number correct


Francisco Aspillaga

Recommended Posts

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]

image.thumb.png.ed0cef2e22f5fc80e1cdb1ae398fb1e6.pngIn the image week 47 is actually week 45.

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

Thank you,

Link to comment
Share on other sites

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:

ISOWeek.thumb.png.c553d4cbdd9d5c61476007034d764942.png 

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)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...