Last year's corresponding date's reveneue in query

[Former Member]

Hi,

We have a requirement to present data (from Sales cube 0SD_C03) in following format.Infocube has 0CALDATE and 0BILL_DATE is used to populate billing date in 0CALDATE in update rules.

Billing date Revenue Last year's reveneue

11.08.2006 5000 4000 (for 11.08.2005)

12.08.2006 4000 4000 (for 12.08.2005)

13.08.2006 3000 5000 (for 13.08.2005)

We have defined the query and the data for month August 2006 appears fine. How can be obtain Last year's corresponding date's revenue ?

When we use restricted key figure (with offset -365 on 0CALDAY) to get last year's revenue, query presents the data in two lines, as below.

Billing date Revenue Last year's reveneue

11.08.2006 5000

11.08.2005 4000

How do we get the desired result. Please guide.

Regards,

Piyush

[Former Member]

Hi Piyush,

Some Bad new..you have make this report in Cell Editor only. otherwise i don't think it posible.

I am writing down the step by step sol using cell editor.

1) Create a structure in Rows-> New Selection " Bill Date text variable"->Drag your Billdate user-entry variable.

2) Create a Structure in Columns-> New selection->Name as

"This Year"-< Drag Desired keyfigure.

3) Create a New selection like step 2).

4) click the cell editor button (next to conditon or exception button).

5) Now you will see

-

---

"This Year'----"Last Year"

Bill date_______________rite click->New Selection

6) Drag your Billdate user-entry variable and offset it to -365 and your key figure.

7) Save Query and check the Q

😎 Run.

be Happy:) New learning

Regards,

San!

[Former Member]

Hi San,

After going through iterations, I understand that the variable offset in cell editor would control the data selection but it would still not place last year date (eg revenue for 1/10/2005) data on the same line as current year date's (eg for 1/10/2006) revenue if we use drill down by bill date. The query result puts the data in two different rows.

The issue is, here, data selection as well placement of last year date's revenue have to be controlled.

I think it would work only if I create a row structure with Day1, Day 2...Day 31 and for each cell create new selection with respective day's data selection. This does not require drill down by Bill date. I tried this and it worked (though it is not a very neat solution).

Your comments.

Regards,

Piyush

[Former Member]

HI,

In Rows, drag characteristic 0BILL_DATE.

2) In col, Rite click..new structure and rite clcik on new structure-> New selection. name as " Revenue".

Drag reveune KF and Drag 0CALDATE.

3) Rtie click on New structure and new seelction->"Lats year"->drag rev KF and DRAG 0caldate and apply affset.

hope im lcr.

Regards,

San!

[Former Member]

Hi San !

We tried your method but unfortunately, data does not appear at all in last year's revenue column. Any other suggestion ?

Regards,

Piyush

[Former Member]

Hi Piyush,

Did you try to variable offset on 0FISCYEAR-1

Hope this helps!

[Former Member]

Hi Rian,

I did not try variable offset on 0FISCYEAR-1. Can you please elaborate on what exactly you want me to do ?

Thanks & Regards,

Piyush

After going through iterations, I understand that the variable offset would control the data selection but it would still not place last year date (eg revenue for 1/10/2005) data on the same line as current year date's (eg for 1/10/2006) revenue. The issue is, here, data selection as well placement of last year date's revenue have to be controlled.

Message was edited by: Piyush Patel

[Former Member]

HI,

In Rows, drag characteristic 0BILL_DATE.

2) In col, Rite click..new structure and rite clcik on new structure-> New selection. name as " Revenue".

Drag reveune KF and Drag variable on billdate (Create as replacement path variableon bill_date characteristic)

3) Rite click on New structure and new seelction->"Last year"->drag rev KF and DRAG variable on billdate and apply offset of -365.

Lemme know this works

hope i m clr now.

Regards,

San!

[Former Member]

Hi San,

I followed point 1 & 2.

I am not able to apply offset on replacement path variable - the offset option is input disabled on this variable.

Regards,

Piyush