BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidPhillips2
Rhodochrosite | Level 12

I found a way to calculate the rate of change between years like the screenshot in Enterprise Guide.  What is a good way to display or calculate this type of field in Web Report Studio?  Ideally I would like to keep this in the same information map / cube as the rest of my academic data.


rate of change.png
1 ACCEPTED SOLUTION

Accepted Solutions
DavidPhillips2
Rhodochrosite | Level 12

It turns out that a time hierarchy does not need a date as an input you can fake it.  I used my non date year and assigned it as year in the time selection.  After creating the time hierarchy I was able to easily set rate of change by making a custom field in Web Report Studio.  See the attached link for custom fields that use time hierarchies.

 

http://support.sas.com/kb/33/526.html

View solution in original post

7 REPLIES 7
Matthijs
Obsidian | Level 7

I useally create a/the calculated member in the cube. The calculated member can be added in the information map. That way the measure is created and stored outside the reach of end-users.

DavidPhillips2
Rhodochrosite | Level 12

Yes, but how would I calculate this particular measure.  Please see the screenshot.

Matthijs
Obsidian | Level 7

I use (mdx) PARRALELPERIOD-function.

 

In my case the top row (fall 2011, fall 2012 etc) is a time-dimension. With SAS Cube Studio you can easlily create a calculation based on a time-dimension.

- Click on 'Add member'

- Select 'Time analysis calculation'

- Select 'Compare Parallel Periods'

 

If that doesn't work you can also consider PERIODSTODATE-function in combination with PARALLELPERIOD.

(Sum(PeriodsToDate([Timedimension].[Hierarchie].[Level],[Tijdsdimension].[Hierarchie].CurrentMember),[Measures].[Measurename]) -

Sum(PeriodsToDate([Tijdsdimension].[Hierarchie].[Level],ParallelPeriod([Tijdsdimension].[Hierarchie].[Level], 1,[Tijdsdimensie].[Hierarchie].CurrentMember)),[Measures].[Measurename])) /

Sum(PeriodsToDate([Tijdsdimension].[Hierarchie].[Level],ParallelPeriod([Tijdsdimension].[Hierarchie].[Level], 1,[Tijdsdimension].[Hierarchie].CurrentMember)),[Measures].[Measurename])

 

 

DavidPhillips2
Rhodochrosite | Level 12

Matthjis, that makes sense, I just started playing with the time dimension.  How do I assign something Fall 2011 to a year when it is not a date value?  Ideally I’d like to create a time hierarchy that goes:

Year > Semester  > Gender

If I can do just

Year > Semester  

That adds value too.

I’m getting confused on the dimension designer screen, where I select type.

 

Matthijs
Obsidian | Level 7

A time-hierarchie needs a date value as imput. Does your facttable have a date value?

If yes then perhaps this link can help you build a time hierarchie: http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003229698.htm

(it's an older version, but I'm confident you'll get the point :-))

 

If your facttable doesn't have a date value then perhaps the PrevMember-function wil work. I haven't done this myself, but it looks pretty straight forward.

 

Create Member [CUBE].[Measures].[name_measure_z] AS

([Dimension].[Level].CurrentMember,[Measures].[Measure_name_x] - [Dimension].[Level].PrevMember,[Measures].[Measure_name_x]) / [Dimension].[Level].PrevMember,[Measures].[Measure_name_x]

DavidPhillips2
Rhodochrosite | Level 12

It turns out that a time hierarchy does not need a date as an input you can fake it.  I used my non date year and assigned it as year in the time selection.  After creating the time hierarchy I was able to easily set rate of change by making a custom field in Web Report Studio.  See the attached link for custom fields that use time hierarchies.

 

http://support.sas.com/kb/33/526.html

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
  • 7 replies
  • 1553 views
  • 3 likes
  • 2 in conversation