SUM And PERIODSTODATE Issues! Please Help Me!!!

May 25, 2007

I 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

View 5 Replies


ADVERTISEMENT

MDX PeriodsToDate() W/dynamic Parameters

Nov 15, 2005

Hi All,

I'm trying to write a formula (actually, four) which will be used in Panorama NovaView to allow a user to see Revenue (on rows) by Previous Month (Current Year), Previous Month (Previous Year), Current YTD (ending at month in question), Previous YTD (ending at month in question) (on columns -- not necessarily in that order).

Basically, when run anytime during November 2005, the output would look like:

2004 Total | Oct 2004 | 2005 Total | Oct 2005
$ 100,000 | 15,000 | 120,000 | 17,500

The Dimension I'm working with is [Calendar Year], with levels: (All), [Calendar Year], [Calendar Month], [Calendar Week].

I've been focusing on the formula for the first column, since it is the most complex (I think). Here's the jist of what I am trying to do:

SELECT Measures.Revenue ON ROWS,
<<Last Year>>.January : <<Last Year>>.<<LastMonth>> ON COLUMNS
FROM SalesCube

I believe some combination of PeriodsToDate(), LastPeriod() and/or <<Current Year>>.PrevMember & <<Current Month>>.PrevMember is what I need, but I just can't get the syntax right.

Any help would be appreciated.

Thanks,
Ian Field
SD Union-Trib

View 1 Replies View Related

Analysis :: PeriodsToDate Function Return Nothing In Calculated Measures

Aug 28, 2015

I am new one in MDX. Our PeriodsToDate function does not return any value. We have set type property of our Date Dimension as time.Actually CURRENT MEMBER does not return a valid value. So our PeriodsToDate function fail.

With MEMBER [Measures].[YTD  Actual]
AS
Aggregate
(
                PeriodsToDate
                (
                [DimDate].[CalendarHierarchyDateLevel].[Calendar Year]
                ,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER
                )

[code]....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved