Comparing the Current year day to the Prior year day data in webi

[Former Member]

Hello Everyone,

I am trying to create a report in webi which will compare the last year sales to current year sales on day.

i.e 3rd Monday in Jan2010 = 3rd Monday in Jan 2011.

I have create a variable prior year ' =RelativeDate([Calendar day];-(52*7))' Which tells me which day was monday last year.

Claendar Year Prior Year Current year Sales Prior year sales

12/1/2010 12/2/2009 3000

12/2/2010 12/3/2009 5000

12/3/2010 12/4/2009 3000

12/1/2011 12/2/2010 2000

12/2/2011 12/3/2010 4000

But i need the current year sales and the prior year sales to be on the same row.

ex: 12/1/2011 12/2/2010 2000 5000

Is there any way to do that?

Thanks

[Former Member · Accepted solution]

Hi,

Use this formula in Variable editor

vDataMonth =MonthNumberOfYear([Day])

vDataYear =Year([Day])

StatementMonth =MonthNumberOfYear(ToDate(UserResponse("Statement Date:");""))

vStatementYear =Year(ToDate(UserResponse("Statement Date:");""))

Best Regards

Naveen

[Former Member · Accepted solution]

Hi Naveen,

Thanks for the reply, But i am not using any prompts in the report. At the BW side there is a filter that gives only the current year and the prior year data, As the B-user whats to see data for all the days in the current year and prior year.

Anyway i figured it out at last!

This formula works perfectly.

=If(Year([Calendar day]) = Year(CurrentDate());[Calendar day];RelativeDate([Calendar day] ;(52*7)))

Thank you for trying naveen.

Cheers!

S