Cognos Analytics

 View Only

  • 1.  How to calculate First business day of month

    Posted Wed August 16, 2023 09:47 AM

    Hi ,

    I have a requirement that i need to consider values only on first business day of selected month in prompt. That is first business day should not consider weekends and Holidays.

    Say for ex : i am selecting july 2023 ,the it should consider July 3 since july 1 and 2 falls on weekend . If July 3 is holiday then it should consider july 4th.

    I have calendar table which has weekdays and weekends and Holiday table which has list of holidays .

    Using these 2 tables how can i calculate first business day of month. Please suggest

    Thanks in advance.



    ------------------------------
    Pavithra N
    ------------------------------


  • 2.  RE: How to calculate First business day of month
    Best Answer

    Posted Thu August 17, 2023 02:47 AM

    Hi Pavithra,

    You can create a dataitem like this in your prompt query 

    min([Date] for [YearMonth])

     and exclude weekends and holidays in your filter.

    and set that as use value and still have the [YearMonth] as display in the prompt

    Where [Date] and [YearMonth] comes from your calendar table,

    Br Rene

    #IBMChampion



    ------------------------------
    René Kent Nielsen
    Brand manager
    CogniTech A/S
    Herning
    ------------------------------

    Original Message


  • 3.  RE: How to calculate First business day of month

    Posted Thu August 17, 2023 03:04 AM

    Rene gives a good answer if the calendar table includes all days.

    But the opening post is a little unclear. Firstly it says "using these 2 tables", but only one table is mentioned. It also is unclear if the table with holidays and weekends includes all days.



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 4.  RE: How to calculate First business day of month

    Posted Thu August 17, 2023 11:14 AM

    Hi Pavithra,

    I tried this using joins and it seems to be working.

    1. Started of by having an Excel file with one sheet for Date and whether it is a weekday or weekend. You could replicate the column for weekday/weekend using a query calculation with the _day_of_week function.
    2. Then I joined the two tables into a third table.
      • Filtered to show only null, as this way only dates that are not holidays will be populated.
      • Then I added a calculated field for month, to match with the month parameter coming from the query, see below query info
      • WITH 
        DAYS_WITH_HOLIDAY AS 
            (
            SELECT
                month(DAYS_0.Date_) AS C_Date, 
                DAYS_0.Date_ AS Date_, 
                DAYS_0.DayOfWeek AS DayOfWeek, 
                HOLIDAY0.Holiday AS Holiday
            FROM
                2765571786...DAYS_ DAYS_0
                    LEFT OUTER JOIN 1275846912...HOLIDAY HOLIDAY0
                    ON DAYS_0.Date_ = HOLIDAY0.Date_ 
            WHERE 
                DAYS_0.DayOfWeek IN ( 
                    'WEEKDAY' ) AND
                HOLIDAY0.Holiday IS NULL
            )
        SELECT
            DAYS_WITH_HOLIDAY.C_Date AS C0, 
            DAYS_WITH_HOLIDAY.Date_ AS C1, 
            DAYS_WITH_HOLIDAY.DayOfWeek AS C2, 
            DAYS_WITH_HOLIDAY.Holiday AS C3
        FROM
            DAYS_WITH_HOLIDAY
    3. Now in the report I made a query with the Date and month from the DAYS_WITH_HOLIDAY table. Then added a filter for month and finally change the detail aggregation from NONE to MINIMUM
    4. Now you have a table that shows the first business day of the month coming from the prompt.

    Do excuse me as, I am not too good with data modules to see if this could be done within a Data Module, instead of doing it at the report.



    ------------------------------
    Manu G Panicker
    ------------------------------

    Original Message


  • 5.  RE: How to calculate First business day of month

    Posted Thu August 17, 2023 11:15 AM

    Certainly, you can address this requirement by implementing a logic that skips weekends and holidays when selecting values on the first business day of a chosen month. To achieve this:

    1. Identify the selected month.
    2. Determine the first day of the month.
    3. Check if the first day is a weekend (Saturday or Sunday), and if so, advance to the next business day.
    4. Check if the adjusted first day is a holiday; if yes, skip to the next business day.
    5. Use the adjusted first business day to consider values as required.

    By considering weekends and holidays, you ensure accurate data selection on the first business day of the chosen month.



    ------------------------------
    shoaib bhatti
    ------------------------------

    Original Message