Cognos Analytics

Expand all | Collapse all

How to calculate week of a month (instead of year)

  • 1.  How to calculate week of a month (instead of year)

    Posted Fri June 05, 2020 02:47 PM
    Hi everyone, as you know, the "week_of_year" function shows the week number of the year. What I'd like to show is the week of the month (1, 2, 3, 4 or 5 depending on the month). Has anyone tried to do this in Cognos? We're running CA 11.1 R6. I would appreciate any help with this type of calculation. Thanks so much! Regards, Melody

    ------------------------------
    MELODY ALBERTI
    ------------------------------


  • 2.  RE: How to calculate week of a month (instead of year)

    Posted Mon June 08, 2020 05:29 AM
    Hi Melody, 

    I don't think there is a function available in cognos to get the week no by month. One solution I can think of is to create a calculation with expression similar to rank(weekOfYear FOR month) and use that as the week of the month. 

    Hope this helps. 

    Regards

    ------------------------------
    Kiran Passumarthi
    www.linkedin.com/in/kiranpassumarthi
    ------------------------------



  • 3.  RE: How to calculate week of a month (instead of year)

    Posted Mon June 08, 2020 11:27 AM
    Thanks so much Kiran, I will try that.

    ------------------------------
    MELODY ALBERTI
    ------------------------------



  • 4.  RE: How to calculate week of a month (instead of year)

    Posted Tue June 09, 2020 12:48 AM
    Hi Melody,
    That's an interesting little puzzle. Cognos does not provide a simple function for this. So we need to use what is available.
    This is a scalar operation on a date. That makes it at least a low-cost kind of operation.
    You also need to define what you call the first day of the week, is that Monday or is it Sunday?
    Here is a formula that you can use:

    // the date to calculate the week of month for appears 2 times in the formula
    // the second parameter to the _day_of_week defines the start day of the week
    floor (1 +     // week of month starts with 1
           ( _day_of_week (_first_of_month (2020-06-08),1) +    // 1 = Monday see info on _day_of_week
             extract(day, 2020-06-08) -
             2)    // subtract 2, because each of the previous terms are 1-based, and we need 0-based
             / 7 )

    // Henk


    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 5.  RE: How to calculate week of a month (instead of year)

    Posted Tue June 09, 2020 12:36 PM
    Thanks Henk! That is very helpful. Here's what I came up with (if it doesn't work right, I'll look at using your formula):

    _week_of_year ([Created Date]) -
    _week_of_year ( _first_of_month ([Created Date])) + 1

    ------------------------------
    MELODY ALBERTI
    ------------------------------



  • 6.  RE: How to calculate week of a month (instead of year)

    Posted Tue June 09, 2020 10:30 AM
    Hi Melody,

    Just extract day from date and divide by 7 with some rounding should give the result assuming week 1 always start at the first of the month

    int(day ( Date_) / 7 +0.99)

    Regards

    Peter

    ------------------------------
    Peter D'Haeyer
    ------------------------------



  • 7.  RE: How to calculate week of a month (instead of year)

    Posted Tue June 09, 2020 12:36 PM
    Wow Peter, thanks so much! I'll definitely make a note of this formula as well.

    ------------------------------
    MELODY ALBERTI
    ------------------------------



  • 8.  RE: How to calculate week of a month (instead of year)

    Posted Tue June 09, 2020 01:00 PM
    Edited by Elizabeth Griffin Tue June 09, 2020 01:01 PM
    I added a data item in my query using a case statement based off day of month

    CASE ([Day of Month])
    WHEN (1) THEN (1)
    WHEN (2) THEN (1)
    WHEN (3) THEN (1)
    WHEN (4) THEN (1)
    WHEN (5) THEN (1)
    WHEN (6) THEN (1)
    WHEN (7) THEN (1)
    WHEN (8) THEN (2)
    WHEN (9) THEN (2)
    WHEN (10) THEN (2)
    WHEN (11) THEN (2)
    WHEN (12) THEN (2)
    WHEN (13) THEN (2)
    WHEN (14) THEN (2)
    WHEN (15) THEN (3)
    WHEN (16) THEN (3)
    WHEN (17) THEN (3)
    WHEN (18) THEN (3)
    WHEN (19) THEN (3)
    WHEN (20) THEN (3)
    WHEN (21) THEN (3)
    WHEN (22) THEN (4)
    WHEN (23) THEN (4)
    WHEN (24) THEN (4)
    WHEN (25) THEN (4)
    WHEN (26) THEN (4)
    WHEN (27) THEN (4)
    WHEN (28) THEN (4)
    WHEN (29) THEN (5)
    WHEN (30) THEN (5)
    WHEN (31) THEN (5)
    END

    If you were looking for the 52 weeks, you could write a long case statement based of month and day. Not ideal but it works.
    Another idea is to make a new data source with a data module using an excel or text file as a lookup table and link that to your current data source.

    ------------------------------
    Elizabeth Griffin
    ------------------------------



  • 9.  RE: How to calculate week of a month (instead of year)

    Posted Wed June 10, 2020 03:02 AM
    Just for the sake of ease of reading, you could alter your statement to be:

    CASE
      when (Day of Month]) between 1 and 7 then 1
      when (Day of Month]) between 8 and 14 then 2
      when (Day of Month]) between 15 and 21 then 3
      when (Day of Month]) between 22 and 28 then 4
      when (Day of Month]) between 29 and 31 then 5
    else 999
    end

    ------------------------------
    Chris Turner
    ------------------------------