May 26, 2013 at 2:52 pm
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.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply