Dependent dynamic picklist not recalculating

Post Reply
fstuyck
Posts: 18
Joined: Thu Sep 03, 2020 1:59 pm
OLAP Product: TM1
Version: 2.0
Excel Version: office 365

Dependent dynamic picklist not recalculating

Post by fstuyck »

Hi all,

I am working on a loan model.
The goal is to create a dropdown for end month, containing only periods later than the start date (see screenshot).

I have a loan cube with two dimensions: loan name and Loan Input Parameters.
I have a picklist cube where I reference the below subset.
{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [PeriodMonthly] ), 0),[PeriodMonthly].CurrentMember.NAME > [Input loans].([Loan Input Parameters].[Start Month]))}
This should create a list of periods later than the start date of the loan in question.
Picklist cube rule: ['End Month','Value'] = S: 'Subset:PeriodMonthly:Subset name';

I am 95% sure that he subset syntax is correct, but I have the feeling that CurrentMember is not working if a subset is used in a picklist.
Can anyone confirm?
Attachments
Loan_example.png
Loan_example.png (13.13 KiB) Viewed 2231 times
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Dependent dynamic picklist not recalculating

Post by MarenC »

Hi,

So what exactly is the problem? Are there no elements showing in your dropdown list or the wrong elements or something else?

One Observation,

your MDX references a cube value but only references one dimension within that cube?

Some questions,

Have you tested your MDX in the subset editor to check it actually returns what you expect?
Have you tried this in Architect, i.e. create a similar cube view, and if so does it work as expected?
What is the Start Month value in the Input Loans cube?
What are the period monthly dimension member names? 201901, 201902 and so on or 01, 02, 03 and so on?
Have you checked and double checked that all your spelling is correct, subset names etc?

Maren
fstuyck
Posts: 18
Joined: Thu Sep 03, 2020 1:59 pm
OLAP Product: TM1
Version: 2.0
Excel Version: office 365

Re: Dependent dynamic picklist not recalculating

Post by fstuyck »

If I sepcify the other dimension, the subset is not dependent on the rows (different selection per loan).
Example:
{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [PeriodMonthly] ), 0),[PeriodMonthly].CurrentMember.NAME > [Input loans].([Loans].[New loan 5],[Loan Input Parameters].[Start Month]))}
Via the example I can see that the subset updates correctly, the subset editor shows periods starting from 201911 (see screenshot).
However the picklist shows all leaf level periods. So i conclude that the filter part of the MDX statement is not applied.

Workspace, architect and PaFe all give the same results.

Regards,
Felix
Attachments
Subset editor.png
Subset editor.png (5.72 KiB) Viewed 2216 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: Dependent dynamic picklist not recalculating

Post by Wim Gielis »

What is the MDX view definition for that cube view ? (if only selecting a handful of cells to keep things simple)
For instance in Workspace, you can ask for that definition. Maybe it gives an extra pointer.
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
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Dependent dynamic picklist not recalculating

Post by Mark RMBC »

Hi,

You may have to rethink this and create a subset for each New Loan, so let us say for each new loan you create a subset prefixed with EndMonths_ and then the name of the loan, e.g. New Loan 1 subset would = EndMonths_New Loan 1

The MDX in the EndMonths_New Loan 1 would be:

Code: Select all

{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [PeriodMonthly] ), 0),[PeriodMonthly].CurrentMember.NAME > [Input loans].([Loans].[New Loan 1],[Loan Input Parameters].[Start Month]))} 
(You find a way to automate this subset creation as New Loans are Added and/or updated)

Your picklist rule would then be:

Code: Select all

['End Month','Value']=S:
	'Subset:PeriodMonthly:EndMonths_' | !Loans;
Probably not want you want to hear but I suspect this might be your best choice.
Be very interested in better ideas!

regards,

Mark
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: Dependent dynamic picklist not recalculating

Post by PavoGa »

fstuyck wrote: Fri Apr 08, 2022 9:06 am Hi all,

I am working on a loan model.
The goal is to create a dropdown for end month, containing only periods later than the start date (see screenshot).

I have a loan cube with two dimensions: loan name and Loan Input Parameters.
I have a picklist cube where I reference the below subset.
{FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [PeriodMonthly] ), 0),[PeriodMonthly].CurrentMember.NAME > [Input loans].([Loan Input Parameters].[Start Month]))}
This should create a list of periods later than the start date of the loan in question.
Picklist cube rule: ['End Month','Value'] = S: 'Subset:PeriodMonthly:Subset name';

I am 95% sure that he subset syntax is correct, but I have the feeling that CurrentMember is not working if a subset is used in a picklist.
Can anyone confirm?
The problem here is in the part of the MDX that is supposedly picking [Start Month]:

Code: Select all

[Input loans].([Loan Input Parameters].[Start Month]
It is not using the context of the row dimension as desired, but instead is using the currentmember of the dimension. This can be demonstrated by changing your picklist to use {[loanname].currentmember} and it will show you which [Loan Name] element is being using to find [Start Month].

If I have a chance later on and you have not resolved it by then, I'll see if I can figure out how to get the MDX to use the row member in context.
Ty
Cleveland, TN
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: Dependent dynamic picklist not recalculating

Post by Wim Gielis »

Hello Felix,

I see where you are going to but this is not going to work (unfortunately). The fact that you want to "inject" the value of a different element on the same "selected line" into the MDX is failing. I support the idea of creating subsets like Mark RMBC suggested, yet you have 2 options:

- 1 subset per loan
- 1 subset per month element

It depends on what you think is easiest but I would go for the one subset per month element.

A simple TI process can create those 12 subsets per year. If you only have a handful of loans then the first option will be better.
Use a name prefix of ZZZ_... or }_... similar and the end users will not be bothered by that technical operation.

Use the subsets in the picklist rule like you already did. This isn't too bad of a solution I would say. In the past there were cases where I created up to a 1,000 such subsets (with TI) because of the dynamic nature that end users wanted. If you could indicate to TM1 which row/loan you are using (for instance a change event in Excel and PAfE) then this could be flagged and used in an MDX subset. But it should be a solution that is user-specific since 2 users could be using the same logic at the same time. In the end, it's not worth it and restricted to Excel and change events so it's not a good solution either.

It has been suggested before to have a kind of MDX option in SUBNM and picklists but alas, it's not there.
Still very interested whether Ty can make this work in MDX only !
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
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: Dependent dynamic picklist not recalculating

Post by PavoGa »

Wim Gielis wrote: Fri Apr 08, 2022 8:43 pm
It has been suggested before to have a kind of MDX option in SUBNM and picklists but alas, it's not there.
Still very interested whether Ty can make this work in MDX only !
Yeah, I've got my doubts on this one...
Ty
Cleveland, TN
fstuyck
Posts: 18
Joined: Thu Sep 03, 2020 1:59 pm
OLAP Product: TM1
Version: 2.0
Excel Version: office 365

Re: Dependent dynamic picklist not recalculating

Post by fstuyck »

Thanks Wim,

I see now why it does not work and I agree that one subset per month would be the cleanest solution.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Dependent dynamic picklist not recalculating

Post by MarenC »

It depends on what you think is easiest but I would go for the one subset per month element.
How would this work exactly, given that the time dimension appears to be a continuous combination of year and month. And that in the images posted the need appears to be to extend across multiple years? It showed from 201911 to 202006 in the image from the subset editor, which I presume was cropped and didn't show the full picklist?

Maren
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: Dependent dynamic picklist not recalculating

Post by Wim Gielis »

Hi Maren,

You could create an MDX subset, 1 for each month. SubsetCreateByMDX basically.
Of course when extending the years/months, the subset creation process needs to run for the additional months too.
Then the picklist rule in the control cube can use the selected month and return a picklist based on that, with a good naming convention it's not that hard to do.
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
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Dependent dynamic picklist not recalculating

Post by MarenC »

Then the picklist rule in the control cube can use the selected month and return a picklist based on that
Did I miss the bit where a month was selected? :?
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: Dependent dynamic picklist not recalculating

Post by Adam »

Wim Gielis wrote: Mon Apr 11, 2022 8:34 am Hi Maren,

You could create an MDX subset, 1 for each month. SubsetCreateByMDX basically.
Of course when extending the years/months, the subset creation process needs to run for the additional months too.
Then the picklist rule in the control cube can use the selected month and return a picklist based on that, with a good naming convention it's not that hard to do.
I was able to experiment in our lab environment as we could benefit from this, and I am led to believe this is the correct approach. Seems PAW loads each named subset one time when the view is created, therefore it will have one set of results based on the first instance of the view. If you have multiple "line items" where you're collecting details incl. a picklist, you'll need one named subset per line item, which should create the desired user experience.
Take care.
Adam
Post Reply