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

# OBIEE 10g LY YTD returns YTD for past years

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.

• ###### 1. Re: OBIEE 10g LY YTD returns YTD for past years
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...
• ###### 2. Re: OBIEE 10g LY YTD returns YTD for past years
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.

Edited by: user8337346 on Jul 16, 2012 11:09 AM
• ###### 3. Re: OBIEE 10g LY YTD returns YTD for past years
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.