Design Query with multiple years and ytd column

[Former Member]

I'm trying to design a somewhat dynamic query that looks similar to:

-

---

Jan --- Feb --- Mar --- Apr <b>...</b> Dec --- YTD

2002

2003

2004

2005

The problem is two-fold.

First my predecessor had hard-coded the dates for each year (for each column except YTD) but I believe the dates should be dynamic, in the sense that I always receive the last 36 months of history. (So in 2008, I won't be seeing 2002-2004 data).

Next I need to have this Year-to-date total column. What this means is that, if we are in August, we see the total for Jan '03-Jul '03 in the 2003 row, Jan '04 - Jul '04 in the 2004 row, etc.

Any suggestions?

Thanks,

Jill Mayo

[Former Member]

What you need to do is use restricted key figures for the columns where you either restrict to a variable with offsets or customer exit variables. I think it would be easier to use variable offsets. Also, to get the YTD, you can either you an SAP exit variable or create a customer exit variable to calculate the YTD based on the variable value. To get the Month names, you will need to use text variables for the name of the restricted key figures.

thanks,

Jeff

[Former Member]

thanks. I'll try tomorrow morning.

[Former Member]

So far I am not having luck.

I created the restricted key figures using the variable 0Calmonth and offsets (such as Current Month - 1). While this works, I cannot get the data in the order of January, February, March, etc. What Variable should I use for offsets (I’m assuming that 0CALMONTH is incorrect)?

[Former Member]

I would use 0PCALMON with offsets for the number of months back you need. In each column use this variable with the offsets, do not use a different variable. You could also use the SAP variable 0CMONTH and use the same offsets for this variable. This would be more of a automatic run for the current month.

Thanks,

Jeff