Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 MDX ParallelPeriod Function - HELP Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, May 26, 2013 2:52 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 6, 2015 1:40 AM Points: 101, Visits: 233
 Hi to all.I have a calculated measure in one of my cubes.This is named DELTA SALES AMOUNTIt 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 DayThe 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
 Ten Centuries Group: General Forum Members Last Login: Thursday, November 17, 2016 7:24 AM Points: 1,003, Visits: 1,267
 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
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 6, 2015 1:40 AM Points: 101, Visits: 233
 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 = 24If i select MORE ONE YEAR or MORE ONE MONTH or MORE ON DAY...i receive Null valueor 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
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 6, 2015 1:40 AM Points: 101, Visits: 233
 I think to have found a solution.I need to iterate over each member of a set using Existing MDX FunctionIn 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
 Ten Centuries Group: General Forum Members Last Login: Thursday, November 17, 2016 7:24 AM Points: 1,003, Visits: 1,267
 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] ASSUM( 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 ROWSFROM [Adventure Works]`
Post #1458528
 Posted Friday, May 31, 2013 9:19 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 6, 2015 1:40 AM Points: 101, Visits: 233
 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
 Ten Centuries Group: General Forum Members Last Login: Thursday, November 17, 2016 7:24 AM Points: 1,003, Visits: 1,267
 OK, let us know ;)
Post #1459106
 Posted Monday, June 3, 2013 4:44 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 6, 2015 1:40 AM Points: 101, Visits: 233
 Hi.My Time dimension is like this :Dimension name => CalendarHierarchy => DataAAAAMMGGLevel 1 => YearLevel 2 => MonthLevel 3 => DayIf 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
 Ten Centuries Group: General Forum Members Last Login: Thursday, November 17, 2016 7:24 AM Points: 1,003, Visits: 1,267
 I think your should be something like....`WITH MEMBER [Measures].[Test] ASSUM( 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 ROWSFROM [Adventure Works]`Check the [Calendario].[DataAAAAMMGG].[Date] member name though
Post #1459178
 Posted Monday, June 3, 2013 10:46 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 6, 2015 1:40 AM Points: 101, Visits: 233
 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

 Permissions