Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Scheduling report on 2nd working day of every month

    Posted Mon February 06, 2023 10:42 AM
    Hi guys,
    there is a requirement to schedule the report on the 2nd working day of every month.
    If the 1st of the month is Sunday, then the report schedule should be done on the 3rd, Tuesday,
    and there is no calendar table with holidays in the database level, I need help to figure this out in the event studio.

    Thanks!!


    ------------------------------
    P S
    ------------------------------


  • 2.  RE: Scheduling report on 2nd working day of every month

    Posted Mon February 06, 2023 02:45 PM
    Edited by Logan Whitaker Mon February 06, 2023 02:47 PM

    If your agent runs every day, then you can put an expression like the one below into Event Studio. I've provided an explanation of the expression last.


    CURRENT_DATE =
    (
    IF(_DAY_OF_WEEK(_FIRST_OF_MONTH(CURRENT_DATE),1) IN (1,2,3,4)) THEN (_ADD_DAYS(_FIRST_OF_MONTH(CURRENT_DATE),1))
    ELSE
    IF(_DAY_OF_WEEK(_FIRST_OF_MONTH(CURRENT_DATE),1) IN (5,6)) THEN (_ADD_DAYS(_FIRST_OF_MONTH(CURRENT_DATE),3))
    ELSE
    IF(_DAY_OF_WEEK(_FIRST_OF_MONTH(CURRENT_DATE),1) IN (7)) THEN (_ADD_DAYS(_FIRST_OF_MONTH(CURRENT_DATE),2))
    ELSE
    NULL
    )

    • _FIRST_OF_MONTH calculates the date belonging to the first day of the month using whatever date you feed it (we fed it CURRENT_DATE).
    • _DAY_OF_WEEK calculates the day of week number for whatever date you feed it.  You also define what day number you want the week to begin (as some prefer Sunday whereas I prefer Monday).  So the expression in this one considers Monday as Day 1 of the week.  
    • _ADD_DAYS calculates the date after adding +/- days to the date you feed it.  
    • IF statements are pretty basic, so I'm avoiding describing that here.  
    • You'll see there's basically three different lines of logic for the IF statements:
      1. First one says how many days to add if the first day of the month is a Monday, Tuesday, Wednesday or Thursday;
      2. Next a Friday or Saturday;
      3. Last a Sunday.
    • Adjust those lines as needed by modifying the day numbers or number of days to add. You'd modify the bolded areas (first area is the day of week number the first day of month falls on; second one is how many days to add to that day):  IF(_DAY_OF_WEEK(_FIRST_OF_MONTH(CURRENT_DATE),1) IN (1,2,3,4)) THEN (_ADD_DAYS(_FIRST_OF_MONTH(CURRENT_DATE),1))
    • When this expression is in Event Studio, there will only be row(s) found when the current date is equal to the desired day of the month.  That will be what allows your agent to run a report or whatever it does.  Else the agent will be suppressed when the condition on dates isn't true and it will not trigger the report/emails/whatever the agent does next. 



    ​

    ------------------------------
    Logan Whitaker
    ------------------------------

    Original Message


  • 3.  RE: Scheduling report on 2nd working day of every month

    Posted Tue February 07, 2023 03:47 AM
    Hi Logan Whitaker,
    Thank you very much for your logic and explanation, appreciate it.
    I'll go through it and work accordingly.


    Thanks!!!

    ------------------------------
    P S
    ------------------------------

    Original Message


  • 4.  RE: Scheduling report on 2nd working day of every month

    Posted Tue February 07, 2023 07:10 AM
    Hi,

    its not so easy done with the second working day. Dont forget the public holidays and also a calendar of a bank is different. I would prefer to create/ import a seperate database calendar table with your business working days. Normaly the IT department is already haveing this kind of calendar somewhere in the DWH.

    Then you only need to filter the current year/ month and the number of working day = 2 (just an example, depends on your business calender)

    With your business calendar in a database table there are nearly endless other cool possibilities.

    Regards,
    Hendrik

    ------------------------------
    Hendrik Gebert
    Managing Consultant
    ACP Digital Analytics
    ------------------------------

    Original Message