January 28, 2013 at 3:24 am
Hallo,
I'm totally new on computing on SSAS, and quite new to SSAS in general.
I'd like to get some index like
"tell me the total of contracts started in that year, having status different than '101', divided by the total of contracts ended in the previous year, for each agency (row) and for years from 2010 to 2012 (columns)".
The cube features dimensions StartDate, EndDate, Agencies, and Status.
Facts are contracts each one with datestart, dateend, amount, agent ID, and status.
I'm totally blank: where should I start from? KPIs? Aggregations? Computations?
Any help (even the minimal one) will be greatly appreciated.
Thanks in advance
January 28, 2013 at 3:42 am
Not sure there is a great amount of detail to go off but I would create two measures - # of contracts started this year & # of contracts finished this year.
You may need to do this by creating distinct count measures or by creating MDX calculations (its difficult from the details to work out how best to do this)
You can then create MDX such as
WITH MEMBER [MEASURES].[CALCULATED MEMBER] AS
Sum({[Status].[All].Children - [Status].[101]},[MEASURES].[# contracts started this year])
/[MEASURES].[# of contracts finished this year]
SELECT {[YEARS].[2011],[YEARS].[2012]} ON COLUMNS,
[AGENCY] ON ROWS
FROM CUBE
Mack
January 28, 2013 at 3:47 am
Thank you for your reply, I updated my post with some details - but you pointed me in the right direction.
April 11, 2013 at 5:11 pm
Hi,
the total of contracts started in that year, having status different than '101', divided by the total of contracts ended in the previous year
Look into the function "ParallelPeriod" in MDX. You can create a calculated measure for the previous year then divide it by your current measure in your select. Hope this helps if this is still an open issue.
----------------------------------------------------
April 15, 2013 at 1:22 am
thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply