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.
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.
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.
Yes, but how would I calculate this particular measure. Please see the screenshot.
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])
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.
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]
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.
Good to know!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.