TM1 MDX to update current week in a Cube View dynamicly

Post Reply
POVsoul
Posts: 4
Joined: Mon Sep 27, 2021 9:34 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2010

TM1 MDX to update current week in a Cube View dynamicly

Post by POVsoul » Fri Nov 05, 2021 11:58 am

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

User avatar
gtonkin
MVP
Posts: 979
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by gtonkin » Fri Nov 05, 2021 12:50 pm

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.
Last edited by gtonkin on Fri Nov 05, 2021 8:16 pm, edited 1 time in total.

MarenC
Regular Participant
Posts: 178
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by MarenC » Fri Nov 05, 2021 12:57 pm

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

User avatar
gtonkin
MVP
Posts: 979
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by gtonkin » Fri Nov 05, 2021 1:29 pm

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
MVP
Posts: 3535
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by lotsaram » Sat Nov 06, 2021 2:16 pm

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])
)}
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

POVsoul
Posts: 4
Joined: Mon Sep 27, 2021 9:34 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2010

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by POVsoul » Mon Nov 08, 2021 9:57 am

gtonkin wrote:
Fri Nov 05, 2021 12:50 pm
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
Posts: 4
Joined: Mon Sep 27, 2021 9:34 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2010

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by POVsoul » Mon Nov 08, 2021 10:06 am

lotsaram wrote:
Sat Nov 06, 2021 2:16 pm
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])
)}

User avatar
gtonkin
MVP
Posts: 979
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by gtonkin » Mon Nov 08, 2021 11:22 am

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
MVP
Posts: 3535
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by lotsaram » Tue Nov 09, 2021 9:12 am

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])
)}
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

andreykadysh
Posts: 7
Joined: Fri Aug 27, 2021 5:41 pm
OLAP Product: IBM Cognos Planning Analytics
Version: 2.0.9.9
Excel Version: 2016

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by andreykadysh » Mon Nov 15, 2021 2:30 pm

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
Posts: 4
Joined: Mon Sep 27, 2021 9:34 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2010

Re: TM1 MDX to update current week in a Cube View dynamicly

Post by POVsoul » Tue Nov 16, 2021 1:05 pm

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!

Post Reply