TM1 MDX to update current week in a Cube View dynamicly

[POVsoul]

Hello TMOners,

I have a Cube view where there is a Week dimension, I want to create a MDX formulat to automaticly update the View Week dimension to contain the current week.

Is there a way to do it without having to create a TI process?

Thank you in advance

[gtonkin]

I am assuming you have the current week in a subset or control cube somewhere.
If you have it in a subset, you could try the following:

Code: Select all

{TM1Member( [Period].[Period].[Current Month].Item(0),0 ),
TM1SubsetToSet([Period].[Period],"All N")}

That should give you the current week as the first element and the rest from a given subset afterwards.

If you are reading the value from a cube, you probably need to tweak the TM1Member section to retrieve the value from the cube.
Possibly something like this:

Code: Select all

{StrToMember( "[Period].[Period].[" + [Control].([Control Measures].[Current Period],[Control Values].[Value]) + "]"),
TM1SubsetToSet([Period].[Period],"All N")}

p.s. When you save the view, it remembers the element selected so saving it with this week will have the same value appearing next week until the value is no longer in the subset. Typically I edit the vue and invalidate the element it is referring to so that it defaults to index 1 i.e. your current week.
If you only have the one element, no need to do this as prior weeks are not in the subset.
You could also set the current week to a member that will never be part of future sets. After saving the view, change it back and your view should default to the first member in the set.

[MarenC]

Hi George,

I understand the first part of your MDX, but what is the

,TM1SubsetToSet([Period].[Period],"All N")

all about? I don't get it?

Edit: I am guessing it will ensure the current week is first and the other weeks underneath in the subset. To be honest he only asked for the current week!

Maren

[gtonkin]

TM1SubsetToSet simply inserts the members of the referenced subset into the set you are building with your MDX.
I often have a subset with last n Periods and add this with the current period - makes it easier, especially for users on web/mobile etc. where there is not set editor access and the dropdown needs to contain all relevant members.

[lotsaram]

As an alternative to StrToMember you can also do something like

Code: Select all

{Filter(
  {TM1FilterByLevel(
    {TM1SubsetAll([Week].[Week])},
    0
  )},
  [Week].[Week].CurrentMember.Name = [Settings].([SysParams].[SysParams].[Current Week],[Settings Measure].[Settings Measure].[String Value])
)}

[POVsoul]

I am assuming you have the current week in a subset or control cube somewhere.
If you have it in a subset, you could try the following:

Code: Select all

{TM1Member( [Period].[Period].[Current Month].Item(0),0 ),
TM1SubsetToSet([Period].[Period],"All N")}

That should give you the current week as the first element and the rest from a given subset afterwards.

If you are reading the value from a cube, you probably need to tweak the TM1Member section to retrieve the value from the cube.
Possibly something like this:

Code: Select all

{StrToMember( "[Period].[Period].[" + [Control].([Control Measures].[Current Period],[Control Values].[Value]) + "]"),
TM1SubsetToSet([Period].[Period],"All N")}

p.s. When you save the view, it remembers the element selected so saving it with this week will have the same value appearing next week until the value is no longer in the subset. Typically I edit the vue and invalidate the element it is referring to so that it defaults to index 1 i.e. your current week.
If you only have the one element, no need to do this as prior weeks are not in the subset.
You could also set the current week to a member that will never be part of future sets. After saving the view, change it back and your view should default to the first member in the set.

Thank you for your help! I appreciate it... but I found it hard to understand this mdx.I tried adapting it but didnt work for my case and keeps throwing errors for me.For example lets take the second example since my code retreives the value from a cube :

Code: Select all

{StrToMember( "[Period].[Period].[" + [Control].([Control Measures].[Current Period],[Control Values].[Value]) + "]"),
TM1SubsetToSet([Period].[Period],"All N")}

To adapt this code to my dimension Week the mDX will become :

Code: Select all

{StrToMember( "[Week].[Week].[" + [CubeControl].([DimOneControlCube].[CurrentWeek],[Dim2ControlCube].[ElementContainingValue]) + "]"),
TM1SubsetToSet([Week].[Week],"All N")}

Is it Right like this ?

[POVsoul]

As an alternative to StrToMember you can also do something like

Code: Select all

{Filter(
  {TM1FilterByLevel(
    {TM1SubsetAll([Week].[Week])},
    0
  )},
  [Week].[Week].CurrentMember.Name = [Settings].([SysParams].[SysParams].[Current Week],[Settings Measure].[Settings Measure].[String Value])
)}

Thank you for your help ! As I mensionned in the above reply, I still struggle to understand this mdx, I hope I am not asking for so much, but would mind to explain methis MDX with a concrete dimensions and cubes ? because I got confused with abbreviations.
Here is my attempt to explain it :
For my case, I have a cube:[PrincipalCube] with dimension [Week]. Then I have a controle cube containing current week, Name of the cube is : [ControlCube] , and it contains Two dimensions that defines the curentweek value intersection, lets call them [Dim1Control].[CurrentWeek] and [Dim2Control].[ElementValue]. So the MDX will be like this :

Code: Select all

{Filter(
  {TM1FilterByLevel(
    {TM1SubsetAll([PrincipalCube].[Week])},
    0
  )},
  [PrincipalCube].[Week].CurrentMember.Name = [ControlCube].([Dim1Control].[Current Week],[Dim2Control].[ElementValue])
)}

[gtonkin]

What is the structure of your control cube? You would need to specify the dimension/member combination for each dimension to read from the exact address in which your data is stored. If you leave out a dimension, it will not work as desired.

Try get the first part working i.e. the StrToMember - don't forget the closing brace } if you delete the TM1SubsetToSet.

[lotsaram]

The cube a dimension is a member of is irrelevant for set expresseions. Dimensions and hierarchies are independent of cubes and can be reused in multiple cubes. The standard MDX nomeclature is just [dimension].[hierarchy].[member]

So providing you are correct with your control cube structure then you are almost correct, just remove [PrincipalCube] and replace it with [Week]

Code: Select all

{Filter(
  {TM1FilterByLevel(
    {TM1SubsetAll([Week].[Week])},
    0
  )},
  [Week].[Week].CurrentMember.Name = [ControlCube].([Dim1Control].[Current Week],[Dim2Control].[ElementValue])
)}

[andreykadysh]

Hi, this maybe solution for your case:

1) We have a system parameter cube, where current date is defined by the DAYNO function:

Code: Select all

['Current_Day_Number','ME_001']  = S: TRIM(STR(DAYNO(DATE(NOW() , 1)),6,0));

2) In week dimension there are attributes with start and end date of each week, e.g.:
Week: YW_202152
Start: 2021-12-19
End: 2021-12-25

And also there are attributes which are calculated by the function DAYNO (): E.g. for date 2020-01-04 number is 21918, e.g:
Week: YW_202152
Start: 22633
End: 22639

3) To get current week from current day I use the next simple MDX statement:
N_Since is an attribute with start day of the week
N_To is an attribute with end day of the week
ME_001 is a measure in system parameters cube

Code: Select all

{FILTER(
    {FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [G_Period_Week] )}, 0)},
        [}ElementAttributes_G_Period_Week].([G_Period_Week].CurrentMember,[}ElementAttributes_G_Period_Week].[N_To])>
        [SYS_Parameters].([SYS_Parameter].[Current_Day_Number],[SYS_Parameter_Value].[ME_001]))},
    [}ElementAttributes_G_Period_Week].([G_Period_Week].CurrentMember,[}ElementAttributes_G_Period_Week].[N_Since])<
    [SYS_Parameters].([SYS_Parameter].[Current_Day_Number],[SYS_Parameter_Value].[ME_001]))}

[POVsoul]

Thank you lotsaram ,gtonkin and andreykadysh, your explanations helped me make sens of this problem. Thanks to you lotsaram and gtonkin I was able to understand this MDX syntax which was totally new to me and never knew we could do somthing like this IN MDX, I am glad I asked and Glad I found helpful people like you . Thanks Again.

Also Thank you andreykadysh for sharing your solution, I guess this is a best way to go about it just saving the values in attributes, which will help a lot in future evelotions, in case you want to check the values between specific weeks, instead of writting many MDX queries we just can check the attributes.. I'll keep it in Mind thank you!