4 Replies Latest reply on Sep 25, 2015 9:17 AM by Gianni Ceresa

    OBIEE 11g: How to find the 2nd friday of the month

    user6536648

      HI,

       

      How to find the 2nd Friday of the month by using current date in obiee 11g.

       

      Thanks in advance.

       

      Regards,

      MIT.

        • 1. Re: OBIEE 11g: How to find the 2nd friday of the month
          Joel Acha

          If your backend database is Oracle, your easiest option is to create a function and use the OBIEE EVALUATE function to call your DB function.

           

          All your DB function should be doing is returning the value from a cursor similar to this:

           

          SELECT                 NEXT_DAY (LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE), -1)),

                                           'Friday')

                                 + 7

                                    second_Friday

                            FROM DUAL

          • 2. Re: OBIEE 11g: How to find the 2nd friday of the month
            Gianni Ceresa

            Hi MIT,

            You can try with something like this:

            TIMESTAMPADD(SQL_TSI_DAY, (7+6-DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE))), TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE))

             

            It can still be improved to probably avoid 1-2 transformations but at least it does it.

            You will need to check what DAYOFWEEK return in your environment for Friday (in mine Friday = 6) because if it doesn't return 6 for Friday you will need to adjust the formula.

             

            The idea is to find what day is the first day of the month, from that day go to the first Friday (or just stay there if the first day is a Friday). This is done by the 6-DAYOFWEEK( first day of the month ).

            And now you only need to add 7 more days, so it's why there is a 7+6-DAYOFWEEK( first day of the month ) to make it easier to read and understand (instead of 13-... ).

            • 3. Re: OBIEE 11g: How to find the 2nd friday of the month
              Avinash Pentyala

              Hi, Create Dynamic variable with bellow query use in analytics SELECT NEXT_DAY(NEXT_DAY(TRUNC(SYSDATE, 'MONTH') - 1, 'Friday'), 'Friday') SECOND_FDAY FROM dual;

              • 4. Re: OBIEE 11g: How to find the 2nd friday of the month
                Gianni Ceresa

                It looks like the formula skip 1 week when the first day of the month is a Saturday, this one is the correct version:

                 

                TIMESTAMPADD(SQL_TSI_DAY, (7+6-CASE WHEN DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE)) = 7 THEN 0 ELSE DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE)) END), TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE))