May 27, 2013 at 3:21 am
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
May 27, 2013 at 3:48 pm
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...:-)
May 30, 2013 at 1:50 pm
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]})
May 31, 2013 at 12:29 am
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]
May 31, 2013 at 9:19 am
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.
June 3, 2013 at 1:20 am
OK, let us know 😉
June 3, 2013 at 4:44 am
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.
June 3, 2013 at 4:48 am
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
June 3, 2013 at 10:46 am
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.
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply