Question:
I'm stuck on this requirement. this may sound simple but I just could not figure out how to do it. I hope someone has a solution or example.
I need to compare sales of this year vs last year by months and I don't want to create 36 separate key figures to do this i.e. 12 last year, 12 this year and 12 delta.
the report layout is like this
(month) 1 2 3 4 5 6 ...
mat1 20 10 30 40 39 20 ...
mat2 30 20 30 59 34 20 ...
....
The secondary issue to this is how do I get the months into the delta column headers, e.g. "01/2003 delta". Tried using text variable (replacement path) on the calculated delta unsuccessfully since calmonth is not in the calculated kf
There are 3 time characteristics in the cube calmonth, calyear and calweek
thanks in advance
Answer:
Above your KF's put in the Time Characteristics of Calender Year and Calender Month. (Cal Month under Cal Yr.)
Restrict Calender Year by Current Calender year variable.
Right-click on that restriction and select Variable Off-Set, make it -1. Select the current calender year restriction again and place it in again.
This should now give you the offset you are looking for.
Alternatively you could add in your KF's and do a selection, one with KF1 and the Current Calender Year by month, and then create another selection with KF1 and do it with the current calender year - 1 and calender month.
With the 2nd solution you can do more math to get % diff's and such.
And for you Calender Year/Month display, you should be able to display text and that wold be just the Month Name.
- Stephen
Answer:
it seems that I would need option 2. However, I don't quite understand it.
Please clarify the following:
- Do I need two KF's?
- Is the month restriction done inside the KF definition?
Thanks
Answer:
it seems that I would need option 2. However, I don't quite understand it.
Please clarify the following:
- Do I need two KF's?
- Is the month restriction done inside the KF definition?
Thanks
No, you need one KF.
Yes, the restriction is done "inside" the KF. You would right-click on the KF Structure and select "New Selection". Here is where you add in your KF, and your restrictions.
Answer:
Stephen, thanks for your ideas. I think I know why I could not build this report. My calmonth char is Year/month. I need to add to my cube calmonth2, just month values, in order to write this report. Let me know if this is a correct assumption.
Answer:
Yes, I believe it is for what you want.
- Stephen