3 Replies Latest reply on Jul 16, 2012 9:04 PM by David_T

    OBIEE 10g LY YTD returns YTD for past years

    943577
      Hi,

      I am having past data of 2009,2010,2011. When i use LY YTD it returns full data of YTD instead of last year till date.
      For EX:

      A report contains : location, YearToDate, LastYearYearToDate
      YTD : from Jan,01,2012 to July,31,2012
      LYYTD : from Jan,01,2011 to July,31,2011
      For past data LYYTD : from Jan,01,2010 to July,31,2010 it returns data for whole year instead of till July 13 2010.
      Is there any formula i can apply on the column to return only data till todays date for past years?

      For 2011, i applied formula of FILTER(Measure."SALES" USING cAST(Time.Month# as int) <= MONTH(CURRENT_DATE)) and it works only for 2011 and not for past years viz 2010, 2009.

      Please advice
        • 1. Re: OBIEE 10g LY YTD returns YTD for past years
          David_T
          user8337346 wrote:
          Hi,

          I am having past data of 2009,2010,2011. When i use LY YTD it returns full data of YTD instead of last year till date.
          For EX:

          A report contains : location, YearToDate, LastYearYearToDate
          YTD : from Jan,01,2012 to July,31,2012
          LYYTD : from Jan,01,2011 to July,31,2011
          For past data LYYTD : from Jan,01,2010 to July,31,2010 it returns data for whole year instead of till July 13 2010.
          Is there any formula i can apply on the column to return only data till todays date for past years?

          For 2011, i applied formula of FILTER(Measure."SALES" USING cAST(Time.Month# as int) <= MONTH(CURRENT_DATE)) and it works only for 2011 and not for past years viz 2010, 2009.
          This is actually easier to do than you think. You just have to sort of think outside the box for a minute. Consider this:

          1) No matter what the years are, the month ranges are always the same, right? So on the date column in the main filter area, put the filter:

          where MONTH(Table.SalesDate) <= Month(CURRENT_DATE)

          2) For the years, you will have a prompt for year (stored in pv_year) and then you can add this to the main filter:

          where YEAR(Table.SalesDate) = @{pv_year} or YEAR(Table.SalesDate) = @{pv_year}-1

          By putting the filters on the columns in the main filter area (and not using the column filters), the data for the report are automatically restricted to the proper time period.

          3) Now you need two instances of your sales column. In the first one you enter in the fx window:

          FILTER(Measure."SALES" USING YEAR(table.SalesDate = @{pv_year}) for the currrent year's measures, and in the second column, enter

          FILTER(Measure."SALES" USING YEAR(table.SalesDate = @{pv_year}-1) for the previous year's measures.

          That should do it for your report...

          On another note...
          Please advice
          ...it's "please advise," not "please advice."
          • 2. Re: OBIEE 10g LY YTD returns YTD for past years
            943577
            Report Consists of 4 Columns:
            Location, Year, LY Sales, LY YTD Sales
            NJ     2009
                 2010
                 2011
                 2012
            LY Sales i am getting it correctly, But not for LY YTD.

            On LY YTD Sales i am applying column filter has
            FILTER("Facts"."LY YTD Sales" USING cast(Time."Month"as int) <= MONTH(CURRENT_DATE) )

            By doing this, i am not able to achieve correct figures for 2009, 2010 and 2011 years.
            Please Advise

            Edited by: user8337346 on Jul 16, 2012 11:09 AM
            • 3. Re: OBIEE 10g LY YTD returns YTD for past years
              David_T
              user8337346 wrote:
              Report Consists of 4 Columns:
              Location, Year, LY Sales, LY YTD Sales
              NJ     2009
                   2010
                   2011
                   2012
              LY Sales i am getting it correctly, But not for LY YTD.

              On LY YTD Sales i am applying column filter has
              FILTER("Facts"."LY YTD Sales" USING cast(Time."Month"as int) <= MONTH(CURRENT_DATE) )

              By doing this, i am not able to achieve correct figures for 2009, 2010 and 2011 years.
              Please Advise

              Edited by: user8337346 on Jul 16, 2012 11:09 AM
              You didn't do what I said! Repeating your mistake won't make it work. Read what I said above again.