Traditional web-based reporting with SAS BI tools

Rolling 12 Months Time Dimensions

Reply
Frequent Contributor
Posts: 75

Rolling 12 Months Time Dimensions

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.

eg

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

Which had:

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

Or

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])

Or

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.

SAS Employee RMP
SAS Employee
Posts: 52

Rolling 12 Months Time Dimensions

John,

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

Regards,

Frequent Contributor
Posts: 75

Re: Rolling 12 Months Time Dimensions

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.

Post a Question
Discussion Stats
  • 2 replies
  • 1416 views
  • 0 likes
  • 2 in conversation