Changing dimension attribute values based on filter selection

Post Reply
Hector2005
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

Post by Hector2005 »

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.
User avatar
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

Post by orlando »

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
User avatar
PavoGa
MVP
Posts: 616
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

Post by PavoGa »

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:

Code: Select all

filter(TM1SUBSETALL( [Months],
     [Months].[Year_Attribute] = [Year].currentmember.name)
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.

Ty
Ty
Cleveland, TN
Hector2005
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

Post by Hector2005 »

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
Hello Orlando, Sorry I think you have a misconception.

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.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
Hector2005
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

Post by Hector2005 »

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 ?
Hello Wim Gielis, Happy to see you.

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.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
Hector2005
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

Post by Hector2005 »

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 ?
The problem is: My customer wants to see the following:-

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.
Hector2005
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

Post by Hector2005 »

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:

Code: Select all

filter(TM1SUBSETALL( [Months],
     [Months].[Year_Attribute] = [Year].currentmember.name)
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.

Ty
Hello Sir, Sorry but It doesn't work. and It has a problem If I click collapse Subset will be changed.

and this will be not preferred.

Thanks a lot.
Hector2005
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

Post by Hector2005 »

Dear all, Appreciated for your aid.

Please this what I ask for.
Attachments
2.JPG
2.JPG (24.11 KiB) Viewed 8008 times
1.JPG
1.JPG (18.36 KiB) Viewed 8008 times
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
MVP
Posts: 3105
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

Post by Wim Gielis »

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 ?
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
Hector2005
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

Post by Hector2005 »

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.
tomok
MVP
Posts: 2831
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

Post by tomok »

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.
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
PavoGa
MVP
Posts: 616
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

Post by PavoGa »

Hector2005 wrote: Tue Mar 13, 2018 12:16 am
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:

Code: Select all

filter(TM1SUBSETALL( [Months],
     [Months].[Year_Attribute] = [Year].currentmember.name)
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.

Ty
Hello Sir, Sorry but It doesn't work. and It has a problem If I click collapse Subset will be changed.

and this will be not preferred.

Thanks a lot.
Well darn. Guess all those views I have working that operate the way I told you will suddenly stop. :o :roll:

You have gotten some good advice here, with Wim bending over backwards to help. Good luck. :)
Ty
Cleveland, TN
Hector2005
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

Post by Hector2005 »

PavoGa wrote: Wed Mar 14, 2018 2:13 pm
Hector2005 wrote: Tue Mar 13, 2018 12:16 am
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:

Code: Select all

filter(TM1SUBSETALL( [Months],
     [Months].[Year_Attribute] = [Year].currentmember.name)
will make your view dynamic according to the value selected in the [Year] dimension. (the [Year] dimension has to be a title dimension.

Ty
Hello Sir, Sorry but It doesn't work. and It has a problem If I click collapse Subset will be changed.

and this will be not preferred.

Thanks a lot.
Well darn. Guess all those views I have working that operate the way I told you will suddenly stop. :o :roll:

You have gotten some good advice here, with Wim bending over backwards to help. Good luck. :)
Appreciated Sir for your advice.
Post Reply