topic Rolling 12 Months Time Dimensions in SAS Web Report Studio

Rolling 12 Months Time Dimensions

JohnT — Tue, 14 Feb 2012 04:14:16 GMT

Gday,

I'm trying to put some additional filters into a SAS Web Report via an OLAP Cube or Information Map.

I assume I'd have to edit the OLAP cube in DI Studio of OLAP Cube Studio and Add a Calculated Member.

This allows me to apply a Rolling 12 months calculation on one measure. I've got over 20 measures and I want to also do YTD, Prior YTD, Prior Rolling 12 Months, Rolling 3 Months, Prior Rolling 3 Months.

So I could do this process for all the measures for all the calculations I want, but that does feel particularly efficient or good in the long run.

I don't know MDX at all, and a lot of results I've found for this kind of feature, just have a line or two of MDX code, but I'm not able to apply any of them, changing the values to something I have always yields an error.

http://communities.sas.com/thread/12865?tstart=100

Which had:

aggregate(qtd(Re: Sum(YTD(),[MEASURES].[]).CurrentMember))

http://www2.sas.com/proceedings/sugi31/219-31.pdf

Which had (top of page 9):

Aggregate([Time].[Months].CurrentMember.lag(1):[Time].[Months].CurrentMember.lag(12 ) ,[Measures].[Actual])

http://www.bi-notes.com/2011/12/sas-olap-cubes-tailing-time-based-data-dynamic-results/

Which had:

Tail([Time].[YM].[MONTH].AllMembers ,13)

Have I totally missed the point of what I've read in those above links? This was a feature that could trivially be done in Cognos Powerplay Transformer, all that was required was a date variable, unfortunately I don't have that software.

Thanks.

Rolling 12 Months Time Dimensions

RMP — Tue, 14 Feb 2012 16:12:36 GMT

John,

where are you trying to add the MDX code? Please send me an example of the MDX you are trying to write.

Regards,

Re: Rolling 12 Months Time Dimensions

JohnT — Tue, 14 Feb 2012 22:45:11 GMT

Gday RMP,

I've tried a couple of places. Here's the one I was trying to describe in my original post:

In SAS DI Studio

* navigate to the Cube

* right click on cube

* go to Maintain

* click on Calculated Members...

* click on Add on the window that pops up

* select Custom calculations and click on Next

The area which I was attempting to put a variety of different pieces of code was in the Formula box.

Here's a couple of lines of code I tried (after clearing the Format field):

* aggregate(qtd(Sum(YTD(),[MEASURES].[]).CurrentMember)) (with parent dimension left as MEASURES)

* aggregate(qtd(Sum(YTD(),[AccPeriod].[All AccPeriod]).CurrentMember)) (with parent dimension left as MEASURES)

* aggregate(qtd(Sum(YTD(),[MEASURES].[]).CurrentMember)) (with parent dimension left as [AccPeriod].[All AccPeriod])

* aggregate(qtd(Sum(YTD(),[AccPeriod].[All AccPeriod]).CurrentMember)) (with parent dimension left as [AccPeriod].[All AccPeriod])

* Aggregate([Time].[Months].CurrentMember.lag(1):[Time].[Months].CurrentMember.lag(12 ) ,[Measures].[Actual]) (with parent dimension left as MEASURES)

* Aggregate([Time].[Months].CurrentMember.lag(1):[Time].[Months].CurrentMember.lag(12 ) ,[Measures].[Actual]) (with parent dimension left as [AccPeriod].[All AccPeriod])

* Tail([Time].[YM].[MONTH].AllMembers ,13) (with parent dimension left as MEASURES)

* Tail([Time].[YM].[MONTH].AllMembers ,13) (with parent dimension left as [AccPeriod].[All AccPeriod])

I'm sure I've tried more weird and wonderful formulas that I no longer remember, and none of them worked. AccPeriod is defined as a Time dimension.

Thanks.