YTD and Prior YTD revenues in a single line chart

[surgents]

Hi,

we would like to display YTD and Prior YTD month revenues for specific product line which we select by a prompt.

Let's say Golf equipment and current month is Apr, so we want one (blue) line for this year and another (red) line for previous year.

So on x-axis there should probably be only months - Jan, Feb, Mar, Apr and we will have two series - Golf equipment for this year, Golf equipment for previous year.

Is it possible to do this in Report Studio?
Thanks for any hints

[blom0344]

Yes,

Use the year for the series   ;D

Perhaps I am missing the point, but if you bring in the selection through appropriate filters and then use the year for the series you should be okay..

[surgents]

the point is that when i use Month from Time dimension - Time hierarchy, i have 2004/Jan, 2005/Feb as members.

it is like having a crosstab:

Quantity.......2004---2005---2006
---------------------------------
Jan              24         89       11
Feb              163       12        8
...

How to make a query calculation for rows? ...that will return only months without year plus only those months which are until current month (YTD months).
Thanks

[blom0344]

If this is dimensionally structured data (cube / DMR) you should add a YTD dimension that is recalculated in the iqd definition I guess (assuming the cube is refreshed on a minimum monthly basis)

[surgents]

we made a regular dimension Month in Transformer that has number of month as a category code and name of month as description, label and short-name.

now something like: filter months for category-code <= 6 is working

but we need to get that 6 (for june) into filter from some calculation that will use current date.

when we use current month (2008/Jun) from current month hierarchy of Time dimension, it gets into the context of the query and we only get values for Jun 2008.

would it be somehow possible to use current system date and extract the number for month from it in a calculation (because the cube is updated daily and so the current month from system date is the same as current month of data in the cube)?

not sure if i made myself clear...
Thanks again

[surgents]

found it in another topic:

extract(month,current_date)

thanks for your help, blom0344

problem solved

make a regular dimension in the cube
use extract(month,current_date)
with the assumption that dates in the cube are actual

Ciao