Changing dimension attribute values based on filter selection
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Changing dimension attribute values based on filter selection
Hello Professionals, Happy to see you.
Please I have a cube called production, its Measure dimension in row, Month dimension in its column and has a year as a filter.
Month Dim has many elements but we want to use the following elements only: YTD, FRY, Q1, Q2, Q3, Q4
Year Dim has: 2017, 2018, ...
I want when user select specific year like 2017 automatically Makes Month elements in cube viewer as the following :
YTD-2016 FRY-2016 Q1-2017 Q2-2017 Q3-2017 Q4-2017
If 2018:
YTD-2017 FRY-2017 Q1-2018 Q2-2018 Q3-2018 Q4-2018
If 2019:
YTD-2018 FRY-2018 Q1-2019 Q2-2019 Q3-2019 Q4-2019
Explanation: YTD and FRY with previous year then all Quarters with the current year.
I thought that I can use control cubes to change the attributes values of any dimension, But i cannot do it.
Question: How to do the previous case?
Thanks a lot,
Best Regards,
Hector.
Please I have a cube called production, its Measure dimension in row, Month dimension in its column and has a year as a filter.
Month Dim has many elements but we want to use the following elements only: YTD, FRY, Q1, Q2, Q3, Q4
Year Dim has: 2017, 2018, ...
I want when user select specific year like 2017 automatically Makes Month elements in cube viewer as the following :
YTD-2016 FRY-2016 Q1-2017 Q2-2017 Q3-2017 Q4-2017
If 2018:
YTD-2017 FRY-2017 Q1-2018 Q2-2018 Q3-2018 Q4-2018
If 2019:
YTD-2018 FRY-2018 Q1-2019 Q2-2019 Q3-2019 Q4-2019
Explanation: YTD and FRY with previous year then all Quarters with the current year.
I thought that I can use control cubes to change the attributes values of any dimension, But i cannot do it.
Question: How to do the previous case?
Thanks a lot,
Best Regards,
Hector.
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: Changing dimension attribute values based on filter selection
Hi Hector,
why do you need month elements like YTD-2107, Q1-2017, Q1-2018 if you have a separate year dimension?
The elements YTD, Q1,Q2,Q3 etc... should be enough.
Just use your month dimension in the columns. Then you will see the figures of the selected year.
Regards,
orlando
why do you need month elements like YTD-2107, Q1-2017, Q1-2018 if you have a separate year dimension?
The elements YTD, Q1,Q2,Q3 etc... should be enough.
Just use your month dimension in the columns. Then you will see the figures of the selected year.
Regards,
orlando
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Changing dimension attribute values based on filter selection
Unless you are needing the labels to vary because of reporting requirements by the users, Orlando's solution should do it. However, if you really do need the labels to change, another solution is to use an MDX subset on the Month dimension. Add elements to your month dimension for each year/month combination and adding an attribute designating what year each element belongs to. So then:
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.
Ty
Code: Select all
filter(TM1SUBSETALL( [Months],
[Months].[Year_Attribute] = [Year].currentmember.name)
Ty
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Re: Changing dimension attribute values based on filter selection
Hello Orlando, Sorry I think you have a misconception.orlando wrote: ↑Mon Mar 12, 2018 9:03 am Hi Hector,
why do you need month elements like YTD-2107, Q1-2017, Q1-2018 if you have a separate year dimension?
The elements YTD, Q1,Q2,Q3 etc... should be enough.
Just use your month dimension in the columns. Then you will see the figures of the selected year.
Regards,
orlando
I want to just display YTD and FRY with the previous year, and Q1, Q2, Q3, Q4 with the next year.
If I select year = 2017
Month should be displayed as: YTD-2016, FRY-2016 and Q1-2017, Q2-2017, ..., Q4-2017
The user wants to see like that.
Thanks a lot,
Best Regards,
Hector.
-
- MVP
- Posts: 3123
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Changing dimension attribute values based on filter selection
Do you have year-specific elements in the Months dimension, and again years in the Year dimension ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Re: Changing dimension attribute values based on filter selection
Hello Wim Gielis, Happy to see you.Wim Gielis wrote: ↑Mon Mar 12, 2018 5:02 pm Do you have year-specific elements in the Months dimension, and again years in the Year dimension ?
Month Dimension contains elements (Jan to Dec, YTD and FYR)
Year Dimension contains elements (2017, 2018 and 2019)
I want to do this for displaying only, because my customer demands that. But if I do it using MDX if he clicked on YTD collapse the Subset will be changed.
Thanks.
-
- MVP
- Posts: 3123
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Changing dimension attribute values based on filter selection
If Year is a title dimension, how can you show 2 years in your columns ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Re: Changing dimension attribute values based on filter selection
The problem is: My customer wants to see the following:-Wim Gielis wrote: ↑Mon Mar 12, 2018 7:37 pm If Year is a title dimension, how can you show 2 years in your columns ?
in the view where year = 2017
YTD of 2016 and FYR of 2016 and Q1 of 2017, ... , Q4 of 2017
So I want to Merge the year 2016 with YTD and FYR and 2017 with Quarters. That is.
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Re: Changing dimension attribute values based on filter selection
Hello Sir, Sorry but It doesn't work. and It has a problem If I click collapse Subset will be changed.PavoGa wrote: ↑Mon Mar 12, 2018 3:35 pm Unless you are needing the labels to vary because of reporting requirements by the users, Orlando's solution should do it. However, if you really do need the labels to change, another solution is to use an MDX subset on the Month dimension. Add elements to your month dimension for each year/month combination and adding an attribute designating what year each element belongs to. So then:
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.Code: Select all
filter(TM1SUBSETALL( [Months], [Months].[Year_Attribute] = [Year].currentmember.name)
Ty
and this will be not preferred.
Thanks a lot.
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Re: Changing dimension attribute values based on filter selection
Dear all, Appreciated for your aid.
Please this what I ask for.
Please this what I ask for.
- Attachments
-
- 2.JPG (24.11 KiB) Viewed 8272 times
-
- 1.JPG (18.36 KiB) Viewed 8272 times
-
- MVP
- Posts: 3123
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Changing dimension attribute values based on filter selection
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3123
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Changing dimension attribute values based on filter selection
And regarding subsets and selections, as I indicated in the previous topic: you can use MDX expressions. But it will be ugly. Why not create subsets in your month dimension, 1 for each year ? Then the user can choose the subset according to the year chosen. Or create 1 view per year, where correct subsets are assigned to the view. Takes about 5 minutes of work. Instruct the admin user at the customer about how to change the views and subsets.
Or create a solution in TI where a process creates and maintains views and subsets. Shouldn’t be hard to do, you’re the consultant, no ?
Or create a solution in TI where a process creates and maintains views and subsets. Shouldn’t be hard to do, you’re the consultant, no ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Re: Changing dimension attribute values based on filter selection
Dear professionals, Is there a way in Rules I can do to push data into another cube from this cube.
Ex: if I'm in X cube I want to push data to Y cube using Rules.
This will solve my problem.
Thanks a lot,
Best Regards.
Ex: if I'm in X cube I want to push data to Y cube using Rules.
This will solve my problem.
Thanks a lot,
Best Regards.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Changing dimension attribute values based on filter selection
Dear "consultant", please read the manual and/or take a training class. You would know the answer to this and many of your previous questions if you would invest a little time in learning the tool instead of relying on others all the time.Hector2005 wrote: ↑Tue Mar 13, 2018 11:00 am Dear professionals, Is there a way in Rules I can do to push data into another cube from this cube.
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Changing dimension attribute values based on filter selection
Well darn. Guess all those views I have working that operate the way I told you will suddenly stop.Hector2005 wrote: ↑Tue Mar 13, 2018 12:16 amHello Sir, Sorry but It doesn't work. and It has a problem If I click collapse Subset will be changed.PavoGa wrote: ↑Mon Mar 12, 2018 3:35 pm Unless you are needing the labels to vary because of reporting requirements by the users, Orlando's solution should do it. However, if you really do need the labels to change, another solution is to use an MDX subset on the Month dimension. Add elements to your month dimension for each year/month combination and adding an attribute designating what year each element belongs to. So then:
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.Code: Select all
filter(TM1SUBSETALL( [Months], [Months].[Year_Attribute] = [Year].currentmember.name)
Ty
and this will be not preferred.
Thanks a lot.
You have gotten some good advice here, with Wim bending over backwards to help. Good luck.
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
Re: Changing dimension attribute values based on filter selection
Appreciated Sir for your advice.PavoGa wrote: ↑Wed Mar 14, 2018 2:13 pmWell darn. Guess all those views I have working that operate the way I told you will suddenly stop.Hector2005 wrote: ↑Tue Mar 13, 2018 12:16 amHello Sir, Sorry but It doesn't work. and It has a problem If I click collapse Subset will be changed.PavoGa wrote: ↑Mon Mar 12, 2018 3:35 pm Unless you are needing the labels to vary because of reporting requirements by the users, Orlando's solution should do it. However, if you really do need the labels to change, another solution is to use an MDX subset on the Month dimension. Add elements to your month dimension for each year/month combination and adding an attribute designating what year each element belongs to. So then:
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.Code: Select all
filter(TM1SUBSETALL( [Months], [Months].[Year_Attribute] = [Year].currentmember.name)
Ty
and this will be not preferred.
Thanks a lot.
You have gotten some good advice here, with Wim bending over backwards to help. Good luck.