TM1 MDX to update current week in a Cube View dynamicly
-
- 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
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
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
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: TM1 MDX to update current week in a Cube View dynamicly
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:
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:
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.
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")}
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")}
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.
-
- Regular Participant
- Posts: 350
- 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
Hi George,
I understand the first part of your MDX, but what is the
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
I understand the first part of your MDX, but what is the
all about? I don't get it?,TM1SubsetToSet([Period].[Period],"All N")
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
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: TM1 MDX to update current week in a Cube View dynamicly
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.
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.
-
- MVP
- Posts: 3654
- 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
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.
-
- 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
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:That should give you the current week as the first element and the rest from a given subset afterwards.Code: Select all
{TM1Member( [Period].[Period].[Current Month].Item(0),0 ), TM1SubsetToSet([Period].[Period],"All N")}
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: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.Code: Select all
{StrToMember( "[Period].[Period].[" + [Control].([Control Measures].[Current Period],[Control Values].[Value]) + "]"), TM1SubsetToSet([Period].[Period],"All N")}
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")}
Code: Select all
{StrToMember( "[Week].[Week].[" + [CubeControl].([DimOneControlCube].[CurrentWeek],[Dim2ControlCube].[ElementContainingValue]) + "]"),
TM1SubsetToSet([Week].[Week],"All N")}
-
- 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
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.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]) )}
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
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: TM1 MDX to update current week in a Cube View dynamicly
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.
Try get the first part working i.e. the StrToMember - don't forget the closing brace } if you delete the TM1SubsetToSet.
-
- MVP
- Posts: 3654
- 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
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]
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.
-
- Posts: 19
- Joined: Fri Aug 27, 2021 5:41 pm
- OLAP Product: IBM Cognos Planning Analytics
- Version: 2.0.9.19
- Excel Version: 2016
Re: TM1 MDX to update current week in a Cube View dynamicly
Hi, this maybe solution for your case:
1) We have a system parameter cube, where current date is defined by the DAYNO function:
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
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));
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]))}
-
- 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
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!
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!