Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX ParallelPeriod Function - HELP Expand / Collapse
Author
Message
Posted Sunday, May 26, 2013 2:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 PM
Points: 69, Visits: 139
Hi to all.

I have a calculated measure in one of my cubes.
This is named DELTA SALES AMOUNT
It calculates Parallelperiod about SalesAmount.

This calculate measures is based on the dimension Calendar that is time Dimension.

I have a Hirarchy named DateAAAAMMGG that is composed of there levels:

Year
Month
Day

The MDX Expression is that :

'([SalesAmount] - (ParallelPeriod([Calendar].[DateAAAAMMGG].[Year]),[SalesAmount]))'

If i select in my Excel's Pivot Table , the level Year (Ex 2013) and Month (Ex. May) and Day (Ex. 24)
ALL IT WORKS GOOD.
In fact i have as result salesamount about prior year 2012 the same month of may the same day 24..

But if i select the level Year (Ex 2013) and Month (Ex. May) and more Days (Ex. 20-21-22-23-24)
I RECEIVE no results ...(Ex. Null)
I expected to have a result salesamount of prior year 2012 the same month of May and the sum of salesamount of days 20-21-22-23-24....instead nothing ...

Where is my mistake ???
I think it is possible to have results selecting more days using PARALLELPERIOD with a TIME DIMENSION ..Why not ???
Or i have to use different MDX functions ???


Regards in advance.
Post #1456925
Posted Monday, May 27, 2013 3:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:57 AM
Points: 981, Visits: 1,077
Hi,

You should try something on the lines of:

SUM([SalesAmount]) - SUM((ParallelPeriod([Calendar].[DateAAAAMMGG].[Year], 1, [Calendar].[DateAAAAMMGG].CURRENTMEMBER),[SalesAmount]))

Let us know how it goes...
Jon
Post #1457019
Posted Monday, May 27, 2013 3:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 PM
Points: 69, Visits: 139
Hi to all.
Thanks for your kind reply and your interest to my problem.

I tried to write as you suggest to me ,,but it does not solve problem

'SUM([SalesAmount]) - SUM((ParallelPeriod([Calendar].[DateAAAAMMGG].[Year], 1, [Calendar].[DateAAAAMMGG].CURRENTMEMBER),[SalesAmount]))'

Writing this expression it works only if i select:

Year = 2013 and Month = 5 and only ONE DAY Day = 24

If i select MORE ONE YEAR or MORE ONE MONTH or MORE ON DAY...i receive Null value
or better i receive only the value of SUM([Num Entrate]) because all part of expression with PARALLELPERIOD does not able to do a result...

Any suggest ???

I will try to understand better...

Thanks...
Post #1457154
Posted Thursday, May 30, 2013 1:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 PM
Points: 69, Visits: 139

I think to have found a solution.
I need to iterate over each member of a set using Existing MDX Function

In this way we can solve the problem of multi select in a Time Dimension...

Existing [Calendario].[DataAAAAMMGG].[MeseVendita].Members,

([Measures].[Num Entrate] - (

ParallelPeriod([Calendario].[DataAAAAMMGG].[AnnoVendita], 1), [Measures].[Num Entrate])))

In this way if i select a Year a 2 Months , I can sum all months of previuos year.
Otherwise PARALLELPERIOD give an error ...

I hope it can be useful for othhers people...


You can use that function also with this example in Adventure Works Cube..
Here is an example...




WITH

MEMBER

[Measures].[Test] AS

SUM

(Existing [Date].[Calendar].[Month].Members,

([Measures].[Order Count] - (

ParallelPeriod([Date].[Calendar].[Calendar Year], 1), [Measures].[Order Count])))


SELECT

{[Measures].[Test], [Measures].[Order Count]}

on columns

FROM



[Adventure Works]

WHERE

({[Date].[Calendar].[Month].&[2004]&[1],

[Date].[Calendar].[Month].&[2004]&[2]})
Post #1458421
Posted Friday, May 31, 2013 12:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:57 AM
Points: 981, Visits: 1,077
Glad you finally solved it, and thanks for getting back with the solution.

I think it works right for you if you go down to the date level of the hierarchy, correct?

WITH MEMBER [Measures].[Test] AS
SUM
(
EXISTING [Date].[Calendar].[Date].Members,
( [Measures].[Order COUNT] - (ParallelPeriod([DATE].[Calendar].[Calendar YEAR], 1), [Measures].[Order COUNT]) )
)
SELECT {[Measures].[Test], [Measures].[Order COUNT]} ON COLUMNS,
[Date].[Calendar].members ON ROWS
FROM [Adventure Works]

Post #1458528
Posted Friday, May 31, 2013 9:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 PM
Points: 69, Visits: 139
Hi thanks for your reply.
As soon as possibile I will test your post...

'My' expression work if i do a MULTI SELECT with YEAR or MONTH Level.
If i select DAY Level , i have to change my expression creating a new Measure...

I will try also yours.

Many thanks regards.
Post #1458757
Posted Monday, June 3, 2013 1:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:57 AM
Points: 981, Visits: 1,077
OK, let us know ;)
Post #1459106
Posted Monday, June 3, 2013 4:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 PM
Points: 69, Visits: 139
Hi.

My Time dimension is like this :

Dimension name => Calendar
Hierarchy => DataAAAAMMGG
Level 1 => Year
Level 2 => Month
Level 3 => Day

If i use that expression you posted , how can i have to write that ???
I tried but i receive error , i think i did not understand good ...sorry

Regards.
Post #1459176
Posted Monday, June 3, 2013 4:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:57 AM
Points: 981, Visits: 1,077
I think your should be something like....

WITH MEMBER [Measures].[Test] AS
SUM
(
EXISTING [Calendario].[DataAAAAMMGG].[Date].Members,
( [Measures].[Order COUNT] - (ParallelPeriod([Calendario].[DataAAAAMMGG].[AnnoVendita], 1), [Measures].[Order COUNT]) )
)
SELECT {[Measures].[Test], [Measures].[Order COUNT]} ON COLUMNS,
[Calendario].[DataAAAAMMGG].members ON ROWS
FROM [Adventure Works]

Check the [Calendario].[DataAAAAMMGG].[Date] member name though
Post #1459178
Posted Monday, June 3, 2013 10:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 PM
Points: 69, Visits: 139
Hi many thanks for your reply.
I will test as soon as possibile...

I am thinking that in my Dimension i do not have a level name Date...
Do you think i need to add in my dimension a level with this name ???
Existing need a LEVEL not a HIERARCHY ...

i WILL try thanks.
Post #1459390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse