• 2 replies
  • Latest Post - ‏2013-02-28T18:06:35Z by DanielWagemann
15496 Posts

Pinned topic Date Functions Question guys =)

‏2013-02-28T16:18:46Z |
I have a report that I want display the max date of Last Friday unless that Friday is a holiday e.g. Good Friday
then the max date would be Thursday
I am assuming I am adding a filter

Are there any business day functions in cognos?

If not I can always create a holidays table

In summary:

Any time the report is ran, it should only show the maximum date of "Last Friday" if it is a business day, otherwise it would be the last Thursday
Updated on 2013-02-28T18:06:35Z at 2013-02-28T18:06:35Z by DanielWagemann
  • SystemAdmin
    15496 Posts

    Re: Date Functions Question guys =)

    Even though you can use a function to determine the day of the week (Friday), there is no function to determine if it is a holiday as this can vary from customer to customer. Holidays in Europe are very different than in the US... even in Canada.
    Your best option is to create a Holiday table, or better yet, to create a robust Date dimension with everything you need in it like;
    Day of week, Day of Year, Month, Week of Year, Current Period, Prior Period, etc...
  • DanielWagemann
    411 Posts

    Re: Date Functions Question guys =)

    As per the previous posters message, a proper time dimension is the way to go.

    Just for fun, here is the calculation to obtain the last Friday...
    _add_days([DateField],((_day_of_week ([DateField],1)-5)*-1))

    With the above in place it would just be an if then else, in clause and another add_days minus one to adjust any Friday holidays.

    Again the right answer is to build a proper time dimension. Do it once, Do it right.
    Updated on 2014-03-25T07:42:35Z at 2014-03-25T07:42:35Z by iron-man