Skip to Main Content
Spotfire Ideas Portal

Add ISOWEEK function to Spotfire

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
  • ADMIN RESPONSE
    Jan 2, 2023

    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.

  • Attach files
      Drop here to upload
    • Guest
      Reply
      |
      Feb 12, 2021

      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

    • Guest
      Reply
      |
      Jun 25, 2019

      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.

    • Guest
      Reply
      |
      Jun 24, 2019

      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

    • Guest
      Reply
      |
      Jun 24, 2019

      Thanks for a great idea. We recently added lag and lead. Hopefully we could add this one too.

    • Guest
      Reply
      |
      Jun 4, 2019

      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