topic Re: Complex Calculation in Web Report Studio Rate of Change in SAS Web Report Studio

Complex Calculation in Web Report Studio Rate of Change

DavidPhillips2 — Tue, 19 Jan 2016 17:10:43 GMT

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.

Re: Complex Calculation in Web Report Studio Rate of Change

Matthijs — Wed, 20 Jan 2016 10:06:01 GMT

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.

Re: Complex Calculation in Web Report Studio Rate of Change

DavidPhillips2 — Wed, 20 Jan 2016 14:35:00 GMT

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

Re: Complex Calculation in Web Report Studio Rate of Change

Matthijs — Thu, 21 Jan 2016 07:45:41 GMT

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

Re: Complex Calculation in Web Report Studio Rate of Change

DavidPhillips2 — Thu, 21 Jan 2016 18:33:46 GMT

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.

Re: Complex Calculation in Web Report Studio Rate of Change

Matthijs — Fri, 22 Jan 2016 07:51:36 GMT

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]

Re: Complex Calculation in Web Report Studio Rate of Change

DavidPhillips2 — Thu, 28 Jan 2016 21:18:00 GMT

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

Re: Complex Calculation in Web Report Studio Rate of Change

Matthijs — Fri, 29 Jan 2016 06:31:55 GMT

Good to know!