SUM And PERIODSTODATE Issues! Please Help Me!!!
May 25, 2007I have a cube built on a single table. This table has a record for each employee. The record contains various information, but I am interested in tunrover. So i am using the start_date, finish_date, cc_descr (cost centre the employee belongs to) and full_name (employees name) fields.
I've broken down these fields with named calculations.
FiscalStartYear, FiscalStartMonth, etc. by using DATEPART(year,DATEADD(month,-3,start_date)), etc. (Fiscal year starts 1st of April). These are then used to create a 'time dimension' called 'StartDate' and within the time dimension is a hierarchy (Called 'FiscalStart', members - FiscalStartYear>cc_descr>full_name.
Every year since the business started a certain ammount of people joined during that year. At this stage all I want to do is have an accumulated total of staff (ignore staff leaving for the moment).
Currently the cube has an 'All Count' measure which is simply a count of all the available rows in the table.
So I'm trying to create a calculation to accumulate the staff 'joins'.
I'm using:
SUM(PERIODSTODATE( [StartDate].[FiscalStart].[Fiscal Start Year],
[StartDate].[FiscalStart].CurrentMember),
[Measures].[All Count])
This isn't achieving what i need! It is accumulating the total's WITHIN the years by cost centre, but I need it accumulated year by year!
I need:
YEAR | AmmountStarted | Cumulative Count
1996 1 1
1997 1 2
1998 4 6
etc...
I'm getting:
YEAR | Cost Centre | AmmountStarted | Cumulative Count
1996 1 1
1997 1 1
1998 XYZ 1 1
ABC 2 3
DEF 1 4
TOTAL 4 4
How do I fix this!?!?!?!
Anything else you need to know? Just ask!
Many thanks in advance,
Karl