Traditional web-based reporting with SAS BI tools

Complex Calculation in Web Report Studio Rate of Change

Accepted Solution Solved
Reply
Super Contributor
Posts: 480
Accepted Solution

Complex Calculation in Web Report Studio Rate of Change

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

Accepted Solutions
Solution
‎01-28-2016 04:18 PM
Super Contributor
Posts: 480

Re: Complex Calculation in Web Report Studio Rate of Change

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


All Replies
Contributor
Posts: 36

Re: Complex Calculation in Web Report Studio Rate of Change

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.

Super Contributor
Posts: 480

Re: Complex Calculation in Web Report Studio Rate of Change

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

Contributor
Posts: 36

Re: Complex Calculation in Web Report Studio Rate of Change

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

 

 

Super Contributor
Posts: 480

Re: Complex Calculation in Web Report Studio Rate of Change

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.

 

Contributor
Posts: 36

Re: Complex Calculation in Web Report Studio Rate of Change

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]

Solution
‎01-28-2016 04:18 PM
Super Contributor
Posts: 480

Re: Complex Calculation in Web Report Studio Rate of Change

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

Contributor
Posts: 36

Re: Complex Calculation in Web Report Studio Rate of Change

Good to know!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 655 views
  • 3 likes
  • 2 in conversation