Skip to Content
author's profile photo Former Member
Former Member

Last year's corresponding date's reveneue in query

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 10, 2006 at 09:13 AM

    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!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2006 at 11:44 AM

    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

    8) Run.

    be Happy:) New learning

    Regards,

    San!

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.