Last Year & This Year comparison in one block from single query (WEBI)

[Former Member]

Dear Experts

We have a requirement in Web Intelligence report to show last 2 years record for comparison of sales revenue (cross tab). We have single query which brings 2 years of data along with sales details.

DATA SOURCE: (columns look alike - sample)

REQUIREMENT: (Sample)

All columns like quarter/month name can be matched but weekend date will be different for each year. We have used context operator to include them in same block but only managed to achieve different results of not wanted (see below).

I understand that this can be achieved using context operators but not sure where it is being missed. Can anyone suggest other options.

We use SAP Business Objects 4.0 SP10 and Oracle as database.

-----

Kishore

[Former Member]

Hi Kishore,

You cannot see previous year data for current weekend date.

So one option is to choose either of the weekend date and month part as the base to show the data of current and previous years.

For example take 6/4 out of 6/4/2015 and show CY and PY data for the same date and month.

If you see your other fields you are showing only month number and quarter number but not the years. So similarly show the day number which is common for both the years.

Else choose the week numbers than week dates as Mahaboob suggested.

[former_member198519]

Nice suggestion by .

But if you are expecting similar kind of request based on this year and last year, I would recommend you to create two filter for This Year and Last Year at the universe level. Accordingly create a date field for both and then use it in the report.

There is a nice blog from DIVYA P

Hope it helps.

- Kuldeep

[mhmohammed]

Hi Kishore,

If you use Weekend Dates as one of the columns, you'll see multiple rows for sure, why don't you try to use a Week Number instead?

If the Week Numbers are different for 2 weekend dates which are close to each other, then you'll still see multiple rows. Apart from that issue, one way of getting to reduce the rows is to create variables for Measures for Curr Year, Prior Year and manually add columns.

v_Measure_Curr Year = [Measure] where (Year([Weekend Date])=Year(CurrentDate()))

v_Measure_Prior Year = [Measure] where (Year([Weekend Date])=Year(CurrentDate())-1)

Makes sense?

Thanks,

Mahboob Mohammed