Percent change versus year ago

[HSheridan]

I'm new to this list and am running series 6.5. We use SQL to provide a flat file to PowerPlay and our primary focus is to publish trend data to major food corporations.

I have been given the task of calculating four measures:

Dollar Share POINT Change vs. Year Ago
Unit Share POINT Change vs. Year Ago”
Dollar Share PERCENT Change vs. Year Ago
Unit Share PERCENT Change vs. Year Ago

We already know to use the 'customized category' in the time dimension to create things like 'Latest Month'. 'Year-to-date', etc, but the problem is that we want this to show up as a measure, not as part of the time dimension. Having the users do it themselves isn't a great idea either.

Since I need to access the current period and the same period a year ago, doing a calculated measure doesn't help (I can't find a date function there). Ideally, I'd like to be able to compare at the month, quarter and year level across all four measures.

Any ideas on how to get the cube to do this? If I do it in SQL, I'm left with a large number of meaures but need them rolled up into the names I listed above in the cube.

Any help would be greatly appreciated. Thanks in advance.

 

[esselinj]

You could use calculated categories in the timedimension. In that case they perform the calculation for each time element you indicate. When you add a new relative time category and you choose a Grouped-option, by default 2 calculations are added.


Jack

 

[HSheridan]

Thanks for the reply Jack. I need to clarify something though. Are you saying that there's a way to use the calculated categories in the time dimension and have them show as measures? This is probably the most important part of my problem - to have those calculations show up as measures and not as time dimension levels.

Can you please provide more detail if this is possible? Thanks again for the reply.

 

[BruceReed]

When you add a time dimension "grouped", transformers adds 4 columns, 2 are actual times, 2 are calculated for you.

For instance adding YTD grouped will add PRIOR YTD, YTD, YTD change (calculated as actual increase), and YTD Growth (% increase). Also you can do your own manual calculations here, but you have to be a bit more careful.

Chapter 3 of the step-by step transfomer deals extensively with this subject. There is quite a bit of information to digest in this chapter.

Good luck

Bruce Reed