validating in TM1

[Aphr0dite]

hi i need help ASAP!

I am trying to do a sort of validation in my application. i am trying to use a rule for the validation its along the lines of...
i have an application that counts for an overall score and for this to happen there is a dimension(indicators) with elements that are scored and the elements used for scoring have picklists its just 3 columns the first 2 calculate by saying [selected no] * [selected no]=[third column].

The second column of the dimension has a limited number of times a certain number can be selected picklist has (0,1,2,3) the top 5 are weighed = 3 and the 2nd top 5=2 and the rest are 1s and the non applicable=0. now what i am trying to do is to limit the number of times 3 and 2 are selected. i added a counter and a rule that says if 3 is selected add 1 to the counter ===>>>
['Counter']=N:IF(['Development_Priority_Weighting']=3,1,STET);

I also included feeders for the count FEEDERS; ['Development_Priority_Weighting']=>['Counter'];
and when the counter is equal to 5 then only enter 1 or 2...now i am having trouble with the second part i tried this but it didn't work

[]=N:if(['All indicators']=5,['Development_Priority_Weighting'] <>3,STET);

please lead me into the light

3 dimensions: project name,indicators and project assessment

[Steve Rowe]

Not 100% sure I understand what you are asking....

I think your problem summarises to something like this.

I have three cells that are populated by picklists, picklist values can be 1, 2 or 3. The sum of the three values must always be less than or equal to 5.

First I think you need to not allow 0 to be a valid entry in your model so that you can easily test for unpopulated entries.

You can't use the sum of the three items to drive the value of item 3 because that would be circular, so you will need to have sums for the fields excluding the one you are testing to get the possible values.

[Field 3 picklist] = If [Sum of Field 1 and 2]<=2 , "1,2,3" , If [Sum of Field 1 and 2]<=3 , "1,2", If [Sum of Field 1 and 2]<=4 , "1", ""))))
[Field 2 picklist] = If [Sum of Field 1 and 3]<=2 , "1,2,3" , If [Sum of Field 1 and 3]<=3 , "1,2", If [Sum of Field 1 and 3]<=4 , "1", ""))))
[Field 1 picklist] = If [Sum of Field 2 and 3]<=2 , "1,2,3" , If [Sum of Field 2 and 3]<=3 , "1,2", If [Sum of Field 2 and 3]<=4 , "1", ""))))

Of course with the above approach the more fields you have the more items in the pick list you have the number of rules required will rapidly scale. I can't immediately see how you could generalise the above.

Cheers

[Aphr0dite]

hi Steve

thanks for the idea but when it comes to the 0's i must allow for their entry because some of the fields to be rated may not apply to that specific project.

and as for the calculation it only happens at the last column because only 2 rows are being calculated to output on the 3rd column.

and as for the restricting/restriction, i am trying to get a rule to not allow the entry of the number 3 for more than 5 times thats why i created a counter to count when the value 3 is added and if the value reaches 5 than the number 3 should not be entered....

[jim wood]

How did you do your pick list? Steve's rules could be used to generate a pick list in a pick list cube. You could use what Steve put above and simplify your counter.

The counter would simply be a logic check to see the value selected. Then you would have a rule in the pick list cube that check against the consolidated level for that counter:

Code: Select all

[Field 3 picklist] = If [Sum of Field 1 and 2]<=2 , "static:1:2:3" , If [Sum of Field 1 and 2]<=3 , "static:1:2", If [Sum of Field 1 and 2]<=4 , "static:1", ""))))
[Field 2 picklist] = If [Sum of Field 1 and 3]<=2 , "statis:1:2:3" , If [Sum of Field 1 and 3]<=3 , "static:1:2", If [Sum of Field 1 and 3]<=4 , "static:1", ""))))
[Field 1 picklist] = If [Sum of Field 2 and 3]<=2 , "static:1:2:3" , If [Sum of Field 2 and 3]<=3 , "static:1:2", If [Sum of Field 2 and 3]<=4 , "static:1", ""))))

You just need to replace the [Sum of Field 2 and 3] with a DB statement to the original cube.

I hope I understood your requirement and I hope this makes sense,

Jim.

[Aphr0dite]

thanks for the reply Jim

I am very new to tm1 i created the subset picklists and from what your saying i should have a pickilst cube where i use the rule calling the main cube DB() in this rule like

[Field 3 picklist] = If DB('Project Assess Matrix',!Project Name V2,!Indicators,'Development_Priority_Weighting')<=2 , "static:1:2:3" , If DB('Project Assess Matrix',!Project Name V2,!Indicators,'Development_Priority_Weighting')<=3 , "static:1:2", If DB('Project Assess Matrix',!Project Name V2,!Indicators,'Development_Priority_Weighting')<=4 , "static:1", ""))));

i am guessing the static values are the values for the picklist that are going into the picklist...

sorry for being dense

[jim wood]

Yes that is the syntax for defining a pick list, whether be in an attribute on in a rule,

Jim.