Hi...

I have previously been using PeriodtoDate in AS2000 to do a running value
calculated member. However after migrating to AS2005 it appears my code
isnt working..

Can anyone assist with the correct mdx I need?

Clint

Hi Clint,

Could you provide more specifics about the MDX you're using, the
structure of the time hierarchy, and the nature of errors or incorrect
results that you get?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thanks for your response!!!

Here is the MDX I am using:
iif(Sum(PeriodsToDate([Period].[Period].[(All)]),
[Measures].[Sales])=0,NULL,Sum(PeriodsToDate([Period].[(All)]),
[Measures].[Sales]))

My Time hierarchy is as follows:

Year

Quarter

- Attribute Relationship - Year

Month

- Attribute Relationship - Quarter

Originally they were of Type : Fiscal Year, Fiscal Quarter and Fiscal Month,
however to enable YTD calculations to work (thanks to you) I changed these
to Year, Quarter and Month..

It appears to work fine for my Year level... So for example:

2005 Sales = 100000

2006 Sales = 100000

What I see on 2006 is 200000 which is correct.. However if I drill into the
heirarchy it is not cumulatine correctly. It did in AS2000...


Clint



"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Clint,

Assuming that the Time Granularity Attribute for the [Measures].[Sales]
measure group is at the Month level, I'll try to create something
similar in Adventure Works - nothing jumps out from your description
just yet..


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Essentially all I need is for the sales measure to keep cumulating. It
should never return to 0 at any level of the time hierarchy. Sales should
continue to add to the previous periods total...

I really appreciate any assistance you could provide Deepak.

Hi Clint,

Still working on PeriodsToDate() in Adventure Works, but in the meantime
maybe you can try this alternate approach:

'iif(Sum(NULL:[Date].[Calendar].CurrentMember,
[Measures].[Sales Amount])=0,NULL,
Sum(NULL:[Date].[Calendar].CurrentMember,
[Measures].[Sales Amount]))',
FORMAT_STRING = 'Currency'

Select {[Measures].[Sales Amount],
[Measures].[RunningSales]} on columns,
[Date].[Calendar].[Calendar Year].Members on rows
from [Adventure Works]
-----------------------------------------------------------
Sales Amount RunningSales
CY 2001 $11,331,808.96 $11,331,808.96
CY 2002 $30,674,773.18 $42,006,582.14
CY 2003 $41,993,729.72 $84,000,311.87
CY 2004 $25,808,962.34 $109,809,274.20

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Thanks for this, and yes it appears to work, however upon drilling into the
date hierarchy the new measure obviously will disappear..

This is a calc member I have created in Adventure Works.. and it cumulates
at the month level but not at all levels..

Sum( PERIODSTODATE([Date].[Calendar].CurrentMember.Parent.Level),
[Measures].[Reseller Sales Amount] )



Clint

Clint,

After experimenting with AS 2005 Adventure Works, there seems to be a
quirk in PeriodsToDate() behavior (versus AS 2000) which I can't quite
explain: if only a [Date].[Calendar] level is specified, but no member,
it returns members from the [Date].[Fiscal] hierarchy instead. So,
explicitly specify [Date].[Calendar].CurrentMember, like:

Sum(PERIODSTODATE([Date].[Calendar].[(All)],
[Date].[Calendar].CurrentMember),
[Measures].[Reseller Sales Amount])
Select {[Measures].[Reseller Sales Amount],
[Measures].[Cumulative Sales Amount]} on 0,
[Date].[Calendar].[Month].Members on 1
from [Adventure Works]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak..... it works fine!!

Of course I have abandoned AS2005's idea of fiscal years and have written
SQL code within a view to construct my fiscal years.. But now that is done
your MDX is perfect. Thanks again for all your help.

Clint
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote