Skip to main content

First Week Number of Year

Steve Jarvis
  • Edited

I have raise a Ticket on this but wanted to share.

I collect weekly data for a report and have the default start date set as @Date.LastMonthStart~. However, I notice that Info Studio is unable to distinguish between the correct first and last week of the year. I base my week number on what is in the Outlook calendar since this is used company wide. As such, Jan 1 should be the first week on the year and should also include 27-31 Dec 2021.

However, as can be seen below, the Logi Info debug indicates these are spilt between Week 53 last year and Week 1 this year even though rdGrpFirstDayOfWeek is 12/27/2021.

Date WeekDay WeekNumber rdGrpFirstDayOfWeek
12/1/2021 Wednesday 49 11/29/2021
12/2/2021 Thursday 49 11/29/2021
12/3/2021 Friday 49 11/29/2021
12/4/2021 Saturday 49 11/29/2021
12/5/2021 Sunday 49 11/29/2021
12/6/2021 Monday 50 12/6/2021
12/7/2021 Tuesday 50 12/6/2021
12/8/2021 Wednesday 50 12/6/2021
12/9/2021 Thursday 50 12/6/2021
12/10/2021 Friday 50 12/6/2021
12/11/2021 Saturday 50 12/6/2021
12/12/2021 Sunday 50 12/6/2021
12/13/2021 Monday 51 12/13/2021
12/14/2021 Tuesday 51 12/13/2021
12/15/2021 Wednesday 51 12/13/2021
12/16/2021 Thursday 51 12/13/2021
12/17/2021 Friday 51 12/13/2021
12/18/2021 Saturday 51 12/13/2021
12/19/2021 Sunday 51 12/13/2021
12/20/2021 Monday 52 12/20/2021
12/21/2021 Tuesday 52 12/20/2021
12/22/2021 Wednesday 52 12/20/2021
12/23/2021 Thursday 52 12/20/2021
12/24/2021 Friday 52 12/20/2021
12/25/2021 Saturday 52 12/20/2021
12/26/2021 Sunday 52 12/20/2021
12/27/2021 Monday 53 12/27/2021
12/28/2021 Tuesday 53 12/27/2021
12/29/2021 Wednesday 53 12/27/2021
12/30/2021 Thursday 53 12/27/2021
12/31/2021 Friday 53 12/27/2021
1/1/2022 Saturday 1 12/27/2021
1/2/2022 Sunday 1 12/27/2021
0

Comments

8 comments

Date Votes
  • Blake
    • Logi Forum Guru
    • Logi Info Ninja

    Dates are fun aren't they!?!  I found the best solution is to have a dates table in the database in which almost all of my queries get joined to.  I can give you more details if running dates through a table is a viable solution for you.

    0
  • Steve Jarvis

    Thanks Blake. This could be a good option. How do you set this up and how often? I do have a few default tables but never thought I would need one for week number.

    Dates are always a pain especially since our Mainframe DB is formatted as yyyymmdd. However, I would expect out-of-the-box support for correctly aligned week numbers from Info Studio.

    Much appreciated.

    0
  • Blake
    • Logi Forum Guru
    • Logi Info Ninja

    I hear ya on all of the random formats floating around.  I have a DateDim table that covers this as well.  The idea comes form Ralph Kimball and if you search the net there are some sample scripts.  There is some info at https://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/.  There is so much you can do with this from populating drop downs to find the start or end of a month to normalize dates or easily compare to a future or previous year.

    Once populated it should be pretty static if you add enough years in the future.

    I exported some of my data to send to you but I'm not seeing a way to add a bigger script here.  I added a screenshot of some of the data.
    Here is what the table def looks like

    CREATE TABLE [dbo].[DateDim](

    [DateId] [int] NOTNULL,

    [DateVal] [date] NULL,

    [DayOfWeek] [varchar](10)NULL,

    [WeekOfYear] [int] NULL,

    [CalendarMonth] [varchar](10)NULL,

    [CalendarMonthShort] [varchar](3)NULL,

    [CalendarYear] [int] NULL,

    [CalendarQuarter] [int] NULL,

    [HolidayIndicator] [bit] NULL,

    [WeekendIndicator] [bit] NULL,

    [FirstDayOfMonthIndicator] [bit] NULL,

    [LastDayOfMonthIndicator] [bit] NULL,

    [StandardDate] [char](10)NULL,

    [Day] [varchar](2)NULL,

    [DaySuffix] [varchar](4)NULL,

    [DayOfWeekNumber] [char](1)NULL,

    [DayOfWeekInMonth] [varchar](2)NULL,

    [DayOfWeekInYear] [varchar](2)NULL,

    [DayOfQuarter] [varchar](3)NULL,

    [DayOfYear] [varchar](3)NULL,

    [WeekOfMonth] [varchar](1)NULL,

    [WeekOfQuarter] [varchar](2)NULL,

    [MonthNumber] [int] NULL,

    [MonthOfQuarter] [varchar](2)NULL,

    [QuarterName] [varchar](9)NULL,

    [YearName] [char](7)NULL,

    [MonthYear] [char](10)NULL,

    [MMYYYY] [char](6)NULL,

    [FirstDayOfMonthDateVal] [date] NULL,

    [FirstDayOfMonthDateId] [int] NOTNULL,

    [LastDayOfMonthDateVal] [date] NULL,

    [LastDayOfMonthDateId] [int] NOTNULL,

    [FirstDayOfQuarterDateVal] [date] NULL,

    [FirstDayOfQuarterDateId] [int] NOTNULL,

    [LastDayOfQuarterDateVal] [date] NULL,

    [LastDayOfQuarterDateId] [int] NOTNULL,

    [FirstDayOfYearDateVal] [date] NULL,

    [FirstDayOfYearDateId] [int] NOTNULL,

    [LastDayOfYearDateVal] [date] NULL,

    [LastDayOfYearDateId] [int] NOTNULL,

    [PreviousYearDateVal] [date] NULL,

    [PreviousYearDateId] [int] NOTNULL,

    [FirstDayOfPreviousYearDateVal] [date] NULL,

    [FirstDayOfPreviousYearDateId] [int] NOTNULL,

    [LastDayOfPreviousYearDateVal] [date] NULL,

    [LastDayOfPreviousYearDateId] [int] NOTNULL,

    [WeekdayIndicator] [bit] NULL,

    [Holiday] [varchar](50)NULL

    )
    1
  • Steve Jarvis

    Blake, again this is much appreciated and I shall pass this on to my "DBA" (we don't have one!), so I will probably be running this. I can add this to a annual reminder.

    We are a Mainframe team, Assembler guys, data had been pushed to MySQL and the boss wanted a BI tool to analyze it. However the person that created the DB, whilst doing an excellent job, did create dates in multiple formats including varchar 10! I have had so much fun working with these dates!

    Hitting Ctl-+ a few times enabled me to view your table. Question, does this allow for combining end of year/first of year dates into the same week number?

    Thanks.

    0
  • Blake
    • Logi Forum Guru
    • Logi Info Ninja

    I don't think my version gets what you want, but the beauty of it is that you can control how that works when you populate the table.  Note: you should only have to create this once.  I created mine out to 2050. I found a sample excel generator.  You'll likely have to tell the browser this is a safe download http://www.kimballgroup.com/wp-content/uploads/2014/03/Ch3-SampleDateDim.xls.

     

     

     

    1
  • Blake
    • Logi Forum Guru
    • Logi Info Ninja

    Also a note if you have multiple formats that mean the same thing you could add these to the table and then just simply join to that alternate format and then use the normal DateVal.

    0
  • Steve Jarvis

    Thanks for sharing this extra information, it is much appreciated. I downloaded the Excel, it is pretty darned amazing! I like the idea of joining a table containing date formats. I have been reformatting most of the dates in MySQL.

    I just noticed I had given this the incorrect heading, I have updated it.

    Cheers!!

    0
  • Steve Jarvis

    I believe I may be getting close to a solution for the week splitting between years, I.E. Week 53/1. It looks like this is a MySQL problem. Thanks to Steve Murfitt.

    Adding the following put the dates into the same Week Number.
    WeekOfYear(table.date) As WeekNumber

    However, this put the dates in the last week of the previous year. We are working on how to make the first week of year the one that contains Jan 1.

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post