Spotfire Ideas Portal
Spotfire does not currently support translating dates to ISO (8601) week numbers.
The WEEK([date]) function is based on Windows location settings and does not match with ISO weeknums.
Other programs (e.g. Excel) have separate build-in functions for WEEKNUM() and ISOWEEKNUM()
| Implemented in | 12.2 |
Starting with version 12.2, Spotfire allows to format dates using ISO-8601 weeks and years standard to determine how data in a chart appears, and how it is used in calculations.
Learn more about this enhancement and everything new in this release from the what's new in Spotfire 12.2 page.
Important Note:
For many dates, the user-contributed "ISOweek" package from the open-source R community's CRAN repository generates the same week-of-year result in TERR as it does in open-source R, but there are dates for which the result does _not_ match.
Examples include the following, for example:
2021-02-07
2000-12-31
2003-12-31
2004-12-31
2006-12-31
2008-12-31
2009-12-31
Implementation might be easy, simply copying isoweek from the TERR ISOweek package (https://community.tibco.com/wiki/tibco-spotfirer-tips-tricks-reusable-simple-easy-iso-week-calculation-spotfire). When implemented in Spotfire the function would work for all users and report users with web clients.
I found a work-around by using the following CASE formula. It implements the heuristic from the Wikipedia page on ISO8601 that the ISO week belongs to the year in which the Thursday falls.
CASE
WHEN (Month([DATE])=1) AND ((Week([DATE])=1) OR (Week([DATE])>51)) AND (DayOfWeek(Date(Year([DATE]),1,1))<5) AND (DayOfWeek(Date(Year([DATE]),1,1))>=1) THEN Concatenate(Year([DATE]),"01")
WHEN (Month([DATE])=1) AND ((Week([DATE])=1) OR (Week([DATE])>51)) AND (DayOfWeek(Date(Year([DATE]),1,1))=5) AND (DayOfYear([DATE])<4) THEN Concatenate(Year([DATE]) - 1,"53")
WHEN (Month([DATE])=1) AND ((Week([DATE])=1) OR (Week([DATE])>51)) AND (DayOfWeek(Date(Year([DATE]),1,1))=0) AND (DayOfYear([DATE])=1) THEN Concatenate(Year([DATE]) - 1,"52")
WHEN (Month([DATE])=1) AND ((Week([DATE])=1) OR (Week([DATE])>51)) AND (DayOfWeek(Date(Year([DATE]),1,1))=6) AND (DayOfYear(Date(Year([DATE]) - 1,12,31))=366) AND (DayOfYear([DATE])<3) THEN Concatenate(Year([DATE]) - 1,"53")
WHEN (Month([DATE])=1) AND ((Week([DATE])=1) OR (Week([DATE])>51)) AND (DayOfWeek(Date(Year([DATE]),1,1))=6) AND (DayOfYear(Date(Year([DATE]) - 1,12,31))=365) AND (DayOfYear([DATE])<3) THEN Concatenate(Year([DATE]) - 1,"52")
WHEN (Month([DATE])=12) AND ((Week([DATE])=1) OR (Week([DATE])>52)) AND (DayOfWeek(Date(Year([DATE]),12,31))>=1) AND (DayOfWeek(Date(Year([DATE]),12,31))<4) THEN Concatenate(Year([DATE]) + 1,"01")
WHEN (Month([DATE])=12) AND ((Week([DATE])=1) OR (Week([DATE])>52)) AND (DayOfWeek(Date(Year([DATE]),12,31))=4) THEN Concatenate(Year([DATE]),"53")
WHEN (Month([DATE])=12) AND ((Week([DATE])=1) OR (Week([DATE])>52)) AND ((DayOfWeek(Date(Year([DATE]),12,31))=0) OR (DayOfWeek(Date(Year([DATE]),12,31))=6)) THEN Concatenate(Year([DATE]),"52")
WHEN (Month([DATE])=12) AND ((Week([DATE])=1) OR (Week([DATE])>52)) AND (DayOfWeek(Date(Year([DATE]),12,31))=5) AND (DayOfYear(Date(Year([DATE]),12,31))=366) THEN Concatenate(Year([DATE]),"53")
WHEN (Month([DATE])=12) AND ((Week([DATE])=1) OR (Week([DATE])>52)) AND (DayOfWeek(Date(Year([DATE]),12,31))=5) AND (DayOfYear(Date(Year([DATE]),12,31))=365) THEN Concatenate(Year([DATE]),"52")
ELSE String(YearAndWeek([DATE]))
END
Thanks for a great idea. We recently added lag and lead. Hopefully we could add this one too.
Pieter I did create a custom calc for ISOWeek a long time ago. Haven't tried it recently but happy to share the spk. Feel free to email me - ijames@tibco.com