BookmarkSubscribeRSS Feed
JohnT
Quartz | Level 8

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.

2 REPLIES 2
RMP
SAS Employee RMP
SAS Employee

John,

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

Regards,

JohnT
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.

Thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2562 views
  • 0 likes
  • 2 in conversation