4 Replies Latest reply on Feb 21, 2015 5:20 PM by ZFMA86

    Creating a date between pay periods

    ZFMA86

      Using OBIEE Answers 11.1.1, I'm creating an analysis that needs the dates between pay periods.  Right now it only shows Pay Period Begin Date as 1/25/2015 and Pay Period End Date as 2/7/2015 in two columns.  I need a column formula to give me the dates in between Pay Period Begin Date and Pay Period End Date.

       

      "Field User Tenure"."PP_BD" = begin date

      "Field User Tenure"."PP_ED" = end date

       

       

      So it should look like:

      1/25/2015

      1/26/2015

      1/27/2015

      1/28/2015

      1/29/2015

      1/30/2015

      1/31/2015

      2/1/2015

      2/2/2015

      2/3/2015

      2/4/2015

      2/5/2015

      2/6/2015

      2/7/2015

       

      Thanks for helping.

        • 1. Re: Creating a date between pay periods
          Gianni Ceresa

          OBIEE will never generate any data you don't have in a way or another.

          Do you have stored somewhere all these dates between the begin and end of the pay period?

          If you have them somewhere then you can easily output these dates, if you don't have them and just want every single day you can do it if you have a clean and proper Calendar dimension (at the day level).

          But if somewhere you don't have the data OBIEE is not going to generate rows "from nowhere".

           

          PS: what's the point of displaying a simple list of days between 2 dates? I hope all your users know that between 1/25 and 2/7 there are all these days. If you want to display something else next to these days then the problem is different ...

          • 2. Re: Creating a date between pay periods
            ZFMA86

             

            Do you have stored somewhere all these dates between the begin and end of the pay period?

             

            No - this is what I'd like the formula to generate for me.

             

            PS: what's the point of displaying a simple list of days between 2 dates? I hope all your users know that between 1/25 and 2/7 there are all these days. If you want to display something else next to these days then the problem is different ...

             

            Yes, I'm displaying the dates next to other values that would be between these 2 dates.  I need the individual dates so I can build out line chart views and other calculations.

            • 3. Re: Creating a date between pay periods
              Gianni Ceresa

              And you also don't have a clean Calendar dimension? It doesn't cost anything to have one (in 2 minutes you generate one with all the days for the next 20 years).

              If you don't I would say ... no luck for you: generating non existent data is not something OBIEE can do ...

              It can keeps "nulls" and other things, but if you don't feed it with something first it will never produce rows just by itself (the main job OBIEE does is aggregation, and you look for the opposite).

              • 4. Re: Creating a date between pay periods
                ZFMA86

                Thanks for replying.  Unfortunately no calendar dimension in this Subject Area (and I can't create one since I'm more on the end-user side of the data and not creating the raw rpds).  I really would have thought I could create some logic to force OBIEE into generating these date rows along with corresponding values.  Thanks.