Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX ParallelPeriod Function - HELP


MDX ParallelPeriod Function - HELP

Author
Message
maretix
maretix
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 234
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.
Jonathan Mallia
Jonathan Mallia
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1009 Visits: 1283
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
maretix
maretix
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 234
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...:-)
maretix
maretix
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 234
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]})

Jonathan Mallia
Jonathan Mallia
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1009 Visits: 1283
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]


maretix
maretix
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 234
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.
Jonathan Mallia
Jonathan Mallia
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1009 Visits: 1283
OK, let us know Wink
maretix
maretix
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 234
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.
Jonathan Mallia
Jonathan Mallia
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1009 Visits: 1283
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
maretix
maretix
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 234
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search