Dynamic Picklist

Post Reply
dparkar
Posts: 7
Joined: Fri Jan 17, 2020 10:45 am
OLAP Product: Planning Analytics
Version: 2.0.7
Excel Version: 2017

Dynamic Picklist

Post by dparkar » Mon Jun 22, 2020 10:47 am

Hi Everyone,
My scenario is I have A cube with 3 dimensions
Days, Department & Measure.
In measure i have 3 elements that are picklist by example
Capture.PNG
Capture.PNG (28.6 KiB) Viewed 339 times
.
I have a dimension in which there are opportunity 1 2 3 & 4
I need if i have selected a member in opportunity 1 from the picklist that member should not be available for opportunity 2 picklist.


i have tried the mdx filter option which is not working in this case as i dont have the dimension in my cube which is used as picklist.
thanks in advance

scrumthing
Posts: 38
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: Dynamic Picklist

Post by scrumthing » Mon Jun 22, 2020 1:09 pm

You could maybe try writing rules and using a lookup cube which counts the use of the picklist values and base your subset for the picklist on that.
But I am not sure that it is going to work properly.
There is no OLAP database besides TM1!

Mark RMBC
Community Contributor
Posts: 230
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Dynamic Picklist

Post by Mark RMBC » Mon Jun 22, 2020 1:19 pm

Hi,

Assuming you literally only want to rule where a person is in opport1 they should not appear in opport2 picklist, then I got this to work as follows:

Create a clone of your existing cube but with the person dimension and a measure dimension added, this measure will hold a 1 or 0.

This cloned cube will have a rule along the following lines:

['opport1','Value']=N:

If(!Person @= DB('1_Test','opport1',!h_days,!m_Measure),1,0);

Create a couple of subsets, one all n level people, the other MDX based to filter for employees whose value results in zero for the rule above,
so something like

{Filter(TM1FILTERBYLEVEL( {TM1SUBSETALL( [Person] )}, 0), [1_TestClone].([Person].currentmember, [h_department].[Opport1], [m_Measure].[All_Shifts],[h_days].[All_Days], [Measure].[Value]) =0)}

Then create a picklist cube on the 1_test cube with the rule something like:

['opport1']=S:

'Subset:Person:N_Level_Person';


['opport2']=S:

'Subset:Person:Emps Not in Opp1';

Hopefully that can give you an idea where to start!

regards,

Mark

dparkar
Posts: 7
Joined: Fri Jan 17, 2020 10:45 am
OLAP Product: Planning Analytics
Version: 2.0.7
Excel Version: 2017

Re: Dynamic Picklist

Post by dparkar » Wed Jul 01, 2020 6:42 am

Thanks Mark it worked

Post Reply