BookmarkSubscribeRSS Feed
Quartz | Level 8


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.


Which had:

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


Which had (top of page 9):

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


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.


SAS Employee RMP
SAS Employee


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


Quartz | Level 8

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.




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2 in conversation