TM1 Forum

Posted: **Wed Nov 06, 2024 8:35 am**

MarenC wrote: ↑Mon Nov 04, 2024 2:52 pm I would like to bet there isn't a model out there that doesn't use an if somewhere, be it a TI or a rule.

There will always be IF statements. The point is that a Boolean test is one of the most, if not the most expensive functions you can use in a rule. If you can write a rule with a pure mathematical approach and avoid using an IF then you should.

Posted: **Wed Nov 06, 2024 9:44 am**

There will always be IF statements. The point is that a Boolean test is one of the most, if not the most expensive functions you can use in a rule. If you can write a rule with a pure mathematical approach and avoid using an IF then you should.

Of course, but saying that is a bit different to saying this:

Anyway, you should avoid IFs in TM1 in any case.

Posted: **Wed Nov 06, 2024 10:57 am**

...you should avoid IFs in TM1...

Sentiment is valid. Avoid IFs where possible.

Here is a "nice" feeder example I saw in a previous model:

Code: Select all

#--Feed XYZ Measure
['XYZ Amount','Actual'] =>
	DB(
		IF(
			ATTRS('Period',!Period,'Planning')@='Y' & 
			DB('Cube', !Dim1, ..., !DimN-1, 'XYZ Amount')<>0,
            'Cube',
			''), 
		!Dim1, ..., !DimN-1, 'XYZ %');

How do we avoid IFs?
- In the first instance we are doing an "AND" on two criteria - still not 100% sure if TM1 evaluates both or stops if the first is false.
- If we split into multiple IFs, we are looking up on the Period using an attribute, besides the change in value not triggering feeders, we should rather be using a consolidation of Periods that are flagged as Planning = Y and use the consolidation on the Left. One IF gone.
- The other IF is something I have seen over the years where people try to optimise by feeding non-zero values. TM1 does this by default. We are already specifying the measure on the Left and feeding would only be triggered where it has values, albeit a zero rather than UNDEF. This statement is superfluous imo and could be removed too leaving us with no IFs.

So back to the sentiment: "Avoid IFs in TM1" sounds like solid advice.

Posted: **Wed Nov 06, 2024 3:04 pm**

Avoid IFs where possible.

Seems more reasonable than,

you should avoid IFs in TM1 in any case.

But this seems reasonable too:

As with virtually any computing based system, it is impossible to avoid ifs in TM1.

Posted: **Thu Nov 07, 2024 7:11 am**

Ok guys, let's close #44. I repeat question #45:

What are the differences between this methods of data clear?
ViewZeroOut (view covering the whole cube)
CubeClearData
CubeDestroy+CubeCreate

2 days left

Posted: **Thu Nov 07, 2024 1:30 pm**

Hi,

They will all clear the data from the cube.
CubeDestroy and Create will delete all sandbox data whereas the other methods will not clear or delete those.
CubeClearData is faster than ViewZeroOut of the whole cube (unless maybe you wrap an if around it

)

Maren

Posted: **Thu Nov 07, 2024 1:58 pm**

I don't much participate here but as I finish my lunch I might have a quick stab at this one.

A : ViewZeroout (whole cube) will:
- clear all data from the cube
- take a variable amount of time depending on the volume of data in the cube (could take quite a while of cube is large)
- assuming logging is on for the cube and not disabled will write the clear as individual cell transactions to the tm1s.log

B : CubeClearData will:
- clear all data from the cube
- complete almost instantly regardless of the cube size and volume of data
- not leave any trace i the tm1s.log regardless of the status of transaction logging for the cube

C : CubeDestroy followed by CubeCreate will:
- result in a new empty cube (i.e. same result as ViewZeroOut and CubeClearData)
- complete almost instantly regardless of the cube size and volume of data
- not leave any trace i the tm1s.log regardless of the status of transaction logging for the cube
- also destroy any rule file the cube may have had
- also destroy any public views the cube may have had
- also destroy any private views of the cube from all currently logged in users (for users not logged in with private views I'm honestly not sure. There's an outside chance they may persist)
- also destroy any sandbox data of the cube from all currently logged in users (for users not logged in I'm fairly certain their sandbox data would also be wiped)
- also destroy any cube dependencies which the server may have established in the case that there are "downstream" cubes which reference the recreated cube in their rules (reestablishing the dependencies during future queries could cause locks in the model)

If you have this requirement then take option B. Do not take option C.

Posted: **Fri Nov 08, 2024 3:11 pm**

Correct answer:
Is in great stab by lotsaram: viewtopic.php?p=84096#p84096
But I need to add my 50 cents: CubeDestroy will also delete cube's control cubes (}Picklist, }CellSecurity)

But you will not get a point, being MVP

So, winner of this round is MarenC!

Question #45:
I've created a subset in Architect using "All" button. After I had saved it with "All elements" name, it is shown with static subset icon. Will new elements appear in "All elements" dimension after I add them to the dimension?

: Image 71.png (13 KiB) Viewed 26892 times

Posted: **Fri Nov 08, 2024 9:38 pm**

Yes, parameter 278 in the sub file will have a 1 which dictates that the subset, despite being static, will be populated with all elements in that dimension anytime it is referenced or opened. As such, there also will be no element count parameter 270 in the .sub file or the list of static elements following it.

Posted: **Fri Nov 15, 2024 7:18 pm**

Correct answer:
Yes, they will.

Winner of this round is ascheevel!

Question #46:
ADMIN, SecurityAdmin, DataAdmin have NONE access for all applications in }ApplicationSecurity cube. Will a user from ADMIN role see applications?

Posted: **Mon Nov 18, 2024 2:24 pm**

Posted: **Fri Nov 22, 2024 3:03 pm**

Correct answer:
Yes

Winner of this round is ascheevel! (Well, it was again 50% to guess, and both were correct! You are lucky!)

Question #47:
Which TI function will terminate process and cancel all changes it has done?

Posted: **Mon Nov 25, 2024 2:34 pm**

Hi,

I am going to have a wild stab in the dark and say "ProcessExitByProcessRollback" will terminate a process and cancel all changes it has done.

Maren

Posted: **Tue Nov 26, 2024 10:51 am**

In Versions since 2020 year (may be 2019 - I don't remember exactly) ProcessError() canceles all changes that has done during the process.

In early versions it didn't cancel changes.

Posted: **Fri Nov 29, 2024 4:38 pm**

Correct answer:
ProcessError or ProcessRollback.

@Maren: I have not used "ProcessExitByProcessRollback" before and checked it on different environments: it does not terminate process and does not cancel changes. It looks like either the function itself, or it's description in TM1 Reference is wrong. Thus, if you can prove that it does terminate process and cancel all changes, I'll share this point between you and EP_Explorer.

Meanwhile, winner of this round is EP_explorer!

Question #48:

There is a cube with months as columns. Month 04 and 08 have values, all other months are 0.

: Image 74.png (4.45 KiB) Viewed 25333 times

Write an MDX to show the last month with values (08) and all months before that (01-07).

: Image 75.png (2.78 KiB) Viewed 25333 times

Posted: **Fri Nov 29, 2024 6:01 pm**

This seems a tricky one and I have used my own current test set up to apply this to:

Code: Select all

{EXCEPT(
FILTER( TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0), 
  [Period].currentmember.properties('Period Number') <= TM1Member(TAIL(FILTER(TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0), [CubeName].([Period].currentmember,[Measure].[Value])<>0),1).item(0),0).properties('Period Number') 
), 
       FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, [Period].currentmember.properties('PeriodNumber') = '')
)}

Be interested if there is something easier than this!

Maren

Posted: **Fri Nov 29, 2024 9:38 pm**

How about this:

Code: Select all

{
   [Month].[Month].[01] : 
   StrToMember('[Month].[Month].[' + (Tail(NonEmpty([Month].[Month].[01] : [Month].[Month].[12]), 1)).Item(0).Item(0).Name + ']')
}

Posted: **Fri Dec 06, 2024 3:12 pm**

Correct answer:
The question is from this thread, thanks Marioler for question idea: viewtopic.php?f=3&t=15786

Ty and Wim have provided a good solution there:

Code: Select all

PERIODSTODATE( [Manad].[Total Year].Level, TM1MEMBER( TAIL( 
FILTER( [Manad].[Total Year].children, [cubename].([z.TestRowEntryList].[R01]) > 0), 1).item(0), 0))

@Adam: your MDX has a good idea, but it does not check the cube if value is zero or not

winner of this round is MarenC!

Question #49:

Is there a rule function - analog to Excel's FLOOR? If not, how can we calculate it?

Posted: **Fri Dec 06, 2024 4:27 pm**

Which definition of Floor?

If simply rounding down to nearest integer, INT will achieve this.

Code: Select all

## result of floor will be same as RoundDown(3.7, 0) in excel
['value'] = N: 3.7;
['floor'] = N: INT(['value']);
## expect floor to be 3

If needing to round to the nearest multiple of a significance value, you can subtract the result of MOD(value, significance) from the original value

Code: Select all

## result of floor will be same as FLOOR(21.5, 2) in excel: 
['value'] = N: 21.5;
['significance'] = N: 2;
['floor'] = ['value'] - MOD(['value'], ['significance']);
## expect floor to be 20 but you need to add an if statement when there's a sign mismatch to get same behavior as excel

## result of floor will be similar as FLOOR(21.5, -2) in excel:
['value'] = N: 21.5;
['significance'] = N: -2;
['floor'] = IF(SIGN(['value']) <> SIGN(['significance'], 0, ['value'] - MOD(['value'], ['significance']));
## expect floor to be 0, excel would return #num!

Posted: **Fri Dec 13, 2024 3:46 pm**

Correct answer:
Is here: viewtopic.php?p=84186#p84186
And it is more than I've expected! I like your math answers, Andrew!

winner of this round is ascheevel!

Question #50:

Is there a rule function - analog to Excel's CEILING? If not, how can we calculate it?

TM1 Forum

Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz

Re: Ultimate Long TM1 quiz