Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Exclude Non Working Days

    Posted Thu May 06, 2021 02:21 AM
    Hi Everyone,

    Version: IGN Cognos Analytics 11.1 R3

    I am creating a report and am trying to figure out how to calculate the difference between two date columns whilst not considering non working days.

    For example
    Let's say COLUMN1  has a start date that was consistently Monday 0305/2021 and I want to calculate the number of business days using COLUMN2 as the end date, I should get the following values:

    COLUMN1  COLUMN2 # Working Days
    Monday, 3 May 2021 Monday, 3 May 2021 1
    Monday, 3 May 2021 Tuesday, 4 May 2021 2
    Monday, 3 May 2021 Wednesday, 5 May 2021 3
    Monday, 3 May 2021 Thursday, 6 May 2021 4
    Monday, 3 May 2021 Friday, 7 May 2021 5
    Monday, 3 May 2021 Saturday, 8 May 2021 5
    Monday, 3 May 2021 Sunday, 9 May 2021 5
    Monday, 3 May 2021 Monday, 10 May 2021 6
    Monday, 3 May 2021 Tuesday, 11 May 2021 7
    Monday, 3 May 2021 Wednesday, 12 May 2021 8

    To add another layer of difficulty, I need to also not consider Public Holidays and "unexpected exception dates" (i.e. unplanned days we wrote-off).

    I am a little stumped here and would appreciate guidance.

    If the suggestion is to create a custom query or custom table with these dates, I would need guidance on that too.

    Cheers,
    Jackey

    ------------------------------
    Jackey Tran
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Exclude Non Working Days

    Posted Thu May 06, 2021 02:34 AM

    Hi Jackey,

    what kind of data source are you using?

    We also have this request many times and therefore we solved this issue in our data warehouse. There's a special calendar table with all dates and a yes/no flag column which indicates if the date is a working day or not. Everything gets computed via ETL process.

    Br



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Original Message


  • 3.  RE: Exclude Non Working Days

    Posted Thu May 06, 2021 02:48 AM
    Edited by System Admin Fri January 20, 2023 04:46 PM
    Hi Robert,

    (Pardon my lack of understanding of the terminology - still a first time user)

    I am using my company's own package and in it, we have various date columns.
    So far, I have not seen anything resembling a special calendar; likewise, this calendar is unlikely to cater for those "exception days" and we only consider Australian-based public holidays.

    Seems we do not connect to packages outside of our company (unless I'm mistaken)

    My steps so far was:
    • Created blank Report
    • Selected and opened package
    • Pulled relevant columns into the Report page
    • Created several custom queries and calculations for other simpler, straight-forward Data Items

    I am trying to replicate the Excel equivalent of:
    • =NetworkDays(Start_Date, End_Date, [Holidays/CustomDates])

    Regards,
    Jackey

    ------------------------------
    Jackey Tran
    ------------------------------

    Original Message


  • 4.  RE: Exclude Non Working Days
    Best Answer

    Posted Thu May 06, 2021 06:52 AM

    Hi Jackey,

    I don't know any function in reporting which covers workdays. If you want to just cut out specific weekdays (E.G. weekends) you can use the following technique:

    https://www.ibm.com/support/pages/calculate-weekdays-business-days-or-working-days-between-two-dates-inclusively

    I suggest implementing those workday logic into the data source itself by the IT or BI team.
    Our concept bases on the calendar exception table in our ERP system. Every day - regardless if weekends, public holidays or other non-working days - is included in this table. I then have an ETL process which joins this data to my calendar table and adds a flag to each date (0=no workday, 1=workday).
    A second ETL process starts with the first day in my calendar table and accumulates the flag column. This leads to a workday number column where each day has its own number. If it's not a workday then the date has the number of the previous workday.

    E.G.
    May/03 - 1, May04/ - 2, May/05 - 3 .... May/08 - 5, May/09 – 5. Then just calculate the number of workdays like date number A – date number B.

    Here's an example of your real calendar table

    Br

    Robert

     



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Original Message


  • 5.  RE: Exclude Non Working Days

    Posted Fri May 07, 2021 08:56 AM
    I agree with Robert. 

    If you don't have a calendar table available in your databases somewhere, you could easily create one in Excel and use the built in functions (which I don't remember at this point) and then all you would need to do is mark the holidays.  Then you could upload that file and create a new data module combining the existing package with the new date file.  These type of calculations are extremely difficult to impossible without a date calendar table.

    ------------------------------
    Mike Teegarden
    ------------------------------

    Original Message


  • 6.  RE: Exclude Non Working Days

    Posted Mon May 17, 2021 08:41 PM
    Thank you very much, Robert. Appreciate the information.

    ------------------------------
    Jackey Tran
    ------------------------------

    Original Message


  • 7.  RE: Exclude Non Working Days

    Posted Fri May 28, 2021 03:28 PM

    Ideally, your data source has invested in constructing a calendar and if applicable time  "dimension" table.

    As applicable, those tables should have pre-computed values that you use to include/exclude (i.e. it is the last week of a month).

    Given companies may observe different holidays in different geo's etc, frequently they may use outrigger tables to capture those 1-many attributes.

    Classic kimball methodology pattern.

    Avoid relying on expressions, you will just encumber the systems with continually performing various date math etc.

    https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/calendar-date-dimension/



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


Related Content