Query with the days of a period and the same days of last year

[Former Member]

I need a help.

We have a sales cube with daily values.

We have a query that restricts a month (0CALMONTH) and shows the daily values for this month.

No problem, we have only to put 0CALDAY on the line and the value on the columns.

But we need also to show the values of the same days from last year.

Example:

The restricted month is july/2011.

0CALDAY is on the line, so the query shows the values for

01.07.2011 110,00

02.07.2011 108,00

...

31.07.2011 109,00

How can we show in the same line the values for july/2011 and july/2010?

Example:

July/2010 July/2011

01 95,00 110,00

02 96,00 108,00

..

31 94,00 109,00.

Thanks in advance

César Menezes

[Former Member]

Hi,

You can do it by creating 31 RKF's (Instead of creating 31 customer exit variable) and in first RKF you will restrict 0CALDAY either with user input or current date, and in further RKF's you can use same variable with different offset values.

In the next RKF you will set offset as -364 after that -363 and so on.....

If you are not comfertable with 365 logic then you can simply create a customer exit variable and get the current date from last year after that you can simply use that variable in other RKF's with offset as +1, 2 til..30 etc.

Regards,

Durgesh.

[Former Member]

Thanks for the answers until now.

Surely a Day characteristic (ex. CA_DAY, derived from 0CALDAY) would work.

But we have this requirement in many other cubes (I posted only an example) and it's not feasible to create this characteristic now.

I understood the other suggestions, but the problem is not on selecting the values.

We have two restricted key figures, one for a period (filled by the users) and another one for this period - 12.

They work OK and bring the correct results.

The problem is on presenting the results, because as we have 0CALDAY in the line the results appear on two different lines.

Example:

15.07.2010 950,00

15.07.2011 980,00.

Is ther any way to present the results in the same line?

Example:

15.07.2011 950,00 (last year) 980,00 (today)

or

15.07 950,00 (last year) 980,00 (today)

or any other way?

César

[Former Member]

Hi,

Using Characteristic variable using processing type Customer exit on 0calday you can achieve this.

Drag and drop same KF Value in column twice, give restriction to KF1 with variable value what your enter )calday. KF2 give restriction with customer exit value (Write a code in CMOD to previous year same day) in 0calday.

If you need more clarification pls let us know

-

---

BVR

[Former Member]

Hi

would you be able to populate two digit day value in the backend.

If you could then you can have cmod variables in the column to populate this month and same month last year in a structure and day in the rows.

Thanks

krishnan.