2 Replies Latest reply on Jul 19, 2017 8:57 AM by asim cholas

    How to get previous quarter from Quarter End date

    user652652

      Hi Experts,

       

      We do have quarter end date in table and we want to get previous quarter from there

       

      we have 31/03/2017

                    30/06/2017

                     30/07/2017

                    31/12/2017

       

      We need to calculate previous quarter as well YTD from the selected prompt created on this quarter ..how we can achieve this in OBIEE?

       

      Also can we create time hierarchy on this data ? for two levels only Quarter and Year and then use time series function.

        • 1. Re: How to get previous quarter from Quarter End date
          Christian Berg

          user652652 wrote:

           

          Also can we create time hierarchy on this data ? for two levels only Quarter and Year and then use time series function.

          That in itself is the whole answer to your question. You need properly configured time dimensions - so "Time" = TRUE and valid chronological keys for all levels.

           

          As soon as you have that all the time series functionalities are at your disposal. Including AGO which will allow you to do "Quarter AGO" measures on the quarter level of your time hierarchy.

          • 2. Re: How to get previous quarter from Quarter End date
            asim cholas

            For a quick fix you can use below. It is always better to configure at RPD level.

             

            for Previous quarter end date.

             

            timestampadd(SQL_TSI_MONTH,-3,Date '2017-03-31')

             

            for year start date

             

            Apply a filter for between clause and use the below with CURRENT_DATE for YTD

            timestampadd(SQL_TSI_DAY,(-DAYOFYEAR(Date '2017-03-31'))+1,Date '2017-03-31')

             

            Thanks

            Aj