SSAS MDX SCOPE move CurrentMember to CY2019

Question

SSAS MDX SCOPE move CurrentMember to CY2019

Refa Abay 1

-- 602 VVJT (VVJT)
SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[602]);     --gw 16.10.14
THIS= 
    Iif([Zeit].[Hierarchie J-M-T].CurrentMember.Level.Ordinal<>3,Null,
        ([Zeit].[Hierarchie J-M-T ].CurrentMember.lag(728)
       ,[Zeitanalyse].[Zeitanalyse].[Element].&[101])
    );    
END SCOPE;    


-- 701 VVJP (VVJP)
SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[701]);         
THIS= (ParallelPeriod([Zeit].[Hierarchie J-M-T].[Jahr], 2, [Zeit].[Hierarchie J-M-T].CurrentMember),[Zeitanalyse].[Zeitanalyse].[Element].&[101]);          
END SCOPE;

Because of Corona, we want to fix 2019 = [Zeit].[Hierarchie J-M-T].[Jahr].&[2019] as the base in both subcubes.
Can you help us to move the currentmember via mdx to the 2019'er pendand?

Thanks a lot!

CarrinWu-MSFT 6,891 Reputation points

2021-12-23T07:28:46.553+00:00

Hi @Refa Abay , thanks for your post. Have you try to use time-based calculations? For more information, please refer to MDX TIME CALCULATIONS BUILT TO SCALE.
Refa Abay 1 Reputation point

2021-12-23T09:03:52.277+00:00

Hi @CarrinWu-MSFT ,
Mdx time calculations is exactly the technique we use.
But now we want to make two of our elements solid.
For example, we want .CurrentMember***.PrevMember*** to always be .CurrentMember**.CY2019xx**.

CarrinWu-MSFT 6,891

Hi @Refa Abay , thanks for your reply. You could try to define a Named Query to specify the time. For example, create a new named query Time Calcs using the following definition:

SELECT 0 , 'Year ago'

Then define the calculation for the Year ago member, a member that returns the value for the same period in the previous year. For more information, please refer to MDX Scripting - SCOPE:

Scope( [Time Calcs].[Calc].[Year ago] );  
 -- focus on anything below and including year level  
 Scope( [Date].[Date].MEMBERS,  
 [Date].[Calendar Year].[Calendar Year].MEMBERS );  
 -- jump one year back on both user hierarchies  
 This = ( ParallelPeriod( [Date].[Calendar].[Calendar   
Year],  
 1,  
 [Date].[Calendar].CurrentMember   
),  
 ParallelPeriod( [Date].[Calendar Weeks]  
 .[Calendar Year],  
 1,  
 [Date].[Calendar Weeks]  
 .CurrentMember ),  
 [Time Calcs].[Calc].&[0]  
 );   
 End Scope;  
End Scope;

5 answers

Your answer

CarrinWu-MSFT 6,891 Reputation points

2021-12-23T07:28:46.553+00:00

Hi @Refa Abay , thanks for your post. Have you try to use time-based calculations? For more information, please refer to MDX TIME CALCULATIONS BUILT TO SCALE.
Refa Abay 1 Reputation point

2021-12-23T09:03:52.277+00:00

Hi @CarrinWu-MSFT ,
Mdx time calculations is exactly the technique we use.
But now we want to make two of our elements solid.
For example, we want .CurrentMember***.PrevMember*** to always be .CurrentMember**.CY2019xx**.
CarrinWu-MSFT 6,891 Reputation points

2021-12-24T03:59:01.987+00:00

Hi @Refa Abay , thanks for your reply. You could try to define a Named Query to specify the time. For example, create a new named query Time Calcs using the following definition:

SELECT 0 , 'Year ago'

Then define the calculation for the Year ago member, a member that returns the value for the same period in the previous year. For more information, please refer to MDX Scripting - SCOPE:

Scope( [Time Calcs].[Calc].[Year ago] ); -- focus on anything below and including year level Scope( [Date].[Date].MEMBERS, [Date].[Calendar Year].[Calendar Year].MEMBERS ); -- jump one year back on both user hierarchies This = ( ParallelPeriod( [Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember ), ParallelPeriod( [Date].[Calendar Weeks] .[Calendar Year], 1, [Date].[Calendar Weeks] .CurrentMember ), [Time Calcs].[Calc].&[0] ); End Scope; End Scope;

Answer 1

Alexei Stoyanovsky 3,416

 -- 602 VVJT (VVJT)
 SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[602]);     --gw 16.10.14
 THIS= 
     Iif([Zeit].[Hierarchie J-M-T].CurrentMember.Level.Ordinal<>3,Null,
        [Zeit].[Hierarchie J-M-T ].CurrentMember.Lag( ([Zeit].[Jahr].CurrentMember.Member_Key - 2019) * 364 )
        ,[Zeitanalyse].[Zeitanalyse].[Element].&[101])
     );    
 END SCOPE;    
 -- 701 VVJP (VVJP)
 SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[701]);         
 THIS= (Cousin ( [Zeit].[Hierarchie J-M-T ].CurrentMember, [Zeit].[Hierarchie J-M-T].[Jahr].&[2019] ), [Zeitanalyse].[Zeitanalyse].[Element].&[101]);          
 END SCOPE;

Refa Abay 1 Reputation point

2021-12-24T10:49:54.283+00:00

This answer is brilliant.
Thank you dear @Alexei Stoyanovsky from the bottom of my heart.

Answer 2

Refa Abay 1

Hi @Alexei Stoyanovsky ,

but unfortunately no more results come. Do you possibly have any more ideas about this?
The cube has not(!) been default processed again. Can this be related to that?

Alexei Stoyanovsky 3,416 Reputation points

2021-12-24T11:36:58.343+00:00

See the details for the error for any of the cells for the most basic of the items, the one that doesn't refer to others, It's easier to do with SSMS browser or MDX Studio.
You don't need to do any processing for new MDX expressions to calculate.

Answer 3

Refa Abay 1

My mistake. When copying the hierarchy name, I stupidly produced a space, which made the element unreachable.
Now it works.
Thank you @Alexei Stoyanovsky once again very much.

But unfortunately there is a problem with the content.
After all, the purpose of element 602 is to provide a weekday comparison.
I.e. what we want:
in 01.06.2021 (Tuesday) for element 602 from (weekday) Tuesday, 04.06.2019 (26.202,42)
but now we get the date comparison for both elements:
in 01.06.2021 (Tuesday) for element 602 (80,572.32) from (date) Saturday, 01.06.2019 (80,572.32).

The calculation Lag(728) always resulted in exactly the same weekday in the previous year. Lag(324) the same weekday last year, etc.

Alexei Stoyanovsky 3,416 Reputation points

2021-12-24T17:35:13.983+00:00

For this logic I see only .Lag( ([Zeit].[Jahr].CurrentMember.Member_Key - 2019) * 364 )
Refa Abay 1 Reputation point

2021-12-25T16:27:57.193+00:00

That worked great. I thank you very much.
Alexei Stoyanovsky 3,416 Reputation points

2021-12-25T16:55:04.3+00:00

I've updated the original answer with this formula so you could accept it again :)

Answer 4

Refa Abay 1

Dear @Alexei Stoyanovsky ,

I'm afraid I can't finish and I urgently need help again.
With your kind help, we had set 2019 as the base year.
But the slope in percent 703 VVJP_delta_p (VVJP ? %) is unfortunately not correct, although the calculation seems to work.
The error occurs on MONTH and YEAR level. On the DAY level of element 703 the calculation for the slope works.

Via Mdx (ssms mdx query) I also get correct results, but as soon as I create them via SCOPE something is added up that I unfortunately can not understand.

This is what we (in scope for 703) need:

Can you please help again?
Thank you very much in advance.
Refa

Not working correctly (month and year level)
I have tried everything

Here the three scopes. The 701 and 702 are working good at any level.
But the 703 ist not working on the levels month and year correct. Only the day level looks good.

Alexei Stoyanovsky 3,416 Reputation points

2021-12-30T18:36:55.173+00:00

This has probably something to do with the order of calculations and aggregation. Have you tried defining 703 explicitly via 101, without references to 701 and 702?
Refa Abay 1 Reputation point

2021-12-31T02:02:10.767+00:00

Hi AlexiStoyanovsky,

perfect. Your advice has once again helped a lot.
Thank you very much It works.
613 (percent in weekday comparison) I won't look at it again until the new year.
I wish you a happy new year and good health.
Refa
Alexei Stoyanovsky 3,416 Reputation points

2021-12-31T19:52:35.83+00:00

I got me thinking, why do you use scoped assignments for your Time Utility dimension at all? Why not just create straightforward calculated members?
Refa Abay 1 Reputation point

2021-12-31T20:35:48.37+00:00

Hi @Alexei Stoyanovsky ,

the advantage of having an Time Utility dimension is incredibly practical.
We have about 180 operational measures. To analysis all these measures individually with the time analysis (18 elements) would mean over 2500 metrics.
Thus, the auxiliary Time Utility helps to create dynamic ad-hoc calculated measures including dimensionality.
They are really super handy.
Refa Abay 1 Reputation point

2022-01-01T00:19:22.437+00:00

By the way, we even have a second auxiliary dimension.
There we manage the seasonal elements.
Something like Financial Cal.
Alexei Stoyanovsky 3,416 Reputation points

2022-01-01T14:56:57.38+00:00
You misunderstood me, the question was about the way you coded the dimension, not about its use in general. Why scoped assignments and not just, e.g.,

CREATE MEMBER [Zeitanalyse].[Zeitanalyse].[Element].&[701] AS (ParallelPeriod([Zeit].[Hierarchie J-M-T].[Jahr], 2, [Zeit].[Hierarchie J-M-T].CurrentMember) ,[Zeitanalyse].[Zeitanalyse].[Element].&[101]);
Refa Abay 1 Reputation point

2022-01-01T15:33:44.557+00:00

ah.
Exactly this your code we have used for the 701 eight years as a pre-previous year )-2) comparison.

But now after Corona Epidemi the comparison should always go back to the year, so that the base can remain before(!) Corona time.

The company wants to be able to plan the numbers without the influence of the epidemi.

Was that your question?
Alexei Stoyanovsky 3,416 Reputation points

2022-01-02T18:39:37.423+00:00

Apparently I made a poor choice of an example, but then, a lot of them were pictures :) No, it's not about fixing the base year at 2019 either. It's about why you use the more complex SCOPE functionality instead of straightforwardly defining calculated members.
Refa Abay 1 Reputation point

2022-01-02T23:18:28.637+00:00

hmm, without a complete example, I guess I can't understand it, unfortunately. I am very sorry.
Because, my answer would be then again the content of my previous letter to you.
Namely, that the general SCOPE functionality here provides the necessary dynamic due to the high number of metrics (over 180 base measures). Indeed, the "2019'er" rule must work (as you know) for all our metrics.
What exactly do you mean by "calculated members" please?
Alexei Stoyanovsky 3,416 Reputation points

2022-01-04T11:25:25.59+00:00

Why, just that, calculated members defined with MDX. Your apparent approach of coding the Time Utility dimension includes scoping on its members and assigning a value via MDX formulas. For this to work, the member that you scope on must already exist in the cube. The alternative that came to my mind is to directly define calculated members with a CREATE MEMBER statement.

Answer 5

Refa Abay 1

Hi @Alexei Stoyanovsky ,

do you have an idea how I can add up this element that performs calculations at the day level?
It is always enough only the month level, but still can not simply add the monthly values, but exactly the element "[Time analysis].[Time analysis].[Element].&[602]" returned at the day level must be aggregated.
If you could help me with this, I would be very grateful.

Refa Abay 1

SCOPE (  [Zeitanalyse].[Zeitanalyse].[Element].&[602],[Zeit].[Hierarchie J-M-T].[Monat]);  
    THIS =  
    Aggregate(  
        PeriodsToDate(  
            [Zeit].[Hierarchie J-M-T].[Monat],   
          [Zeit].[Hierarchie J-M-T].CurrentMember 
       ))  ;    BACK_COLOR(THIS) = RGB(122,139,139);/*Vorjahr LightCyan4*/
END SCOPE;

done I'm glad I got it done.

Share via

SSAS MDX SCOPE move CurrentMember to CY2019

5 answers

Your answer