TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

ConsolidatedMax

https://www.tm1forum.com/viewtopic.php?t=15250

Page 1 of 1

ConsolidatedMax

Posted: Wed Apr 15, 2020 6:12 pm
by TM1NB
Hi all,

i've got a problem with using ConsolidatedMax in a Rule. Let's say my Cube is 3d with the Dimensions WS_POS, WS_Zeit and WS_Kennzahl and whenever I look at a C-Element in my WS_POS Dimension, I want to see the Max value.

My rule is like:
['WE-EB-Option'] =
IF(DTYPE( 'WS_POS', !WS_POS) @= 'C'
ConsolidatedMax(2,'WS_Planung',!WS_POS,!WS_Zeit,!WS_Kennzahl),
continue);

Now, what happened is this:

Image

The SUM_POS is correct when I look at the N-Elements at my WS_Zeit Dimenson -> it's the max out of the POS-Elements.
And what I've expected here on my C-Element "KJ-2099" is the same. I want to see there a 68, not a 9, because it should be the max in here.
Where is my fail? Is ConsolidatedMax here the wrong funktion or do I think in the wrong direction?

Thanks!

Re: ConsolidatedMax

Posted: Mon Apr 20, 2020 2:02 pm
by TM1NB
No one? No ideas or hints?

Re: ConsolidatedMax

Posted: Mon Apr 20, 2020 3:24 pm
by ascheevel
I'm not familiar enough with the consolidation functions to speak confidently, but it looks like the consolidation function is doing what I would expect it to do: return the max value of N level values. Taking the max of naturally consolidated values might not be within the capabilities of these functions, but I don't know that. You can get around this assumed limitation, by creating an N level element and writing a rule to populate that value with the consolidated value you want to take the max of, "KJ-2099" in your example, "SUM_ZEIT" in mine pictured below. You would then take the ConsolidatedMax of that value in your C rule for SUM_ZEIT.

Re: ConsolidatedMax

Posted: Tue Apr 21, 2020 8:28 am
by TM1NB
Hi ascheevel,

thanks for your reply!

I've already tested some workarounds, but all of them are getting nasty in some ways. I guess your idea will go in the same direction. In total I have to handel 12 Dimensions with that requirement and of course there are some exceptions, where the rule should not be triggered in that way.
My hope was to find an easy way to solve it, but I guess I can dream on... :)
The problem could be solved so easily by getting the max out of the consolidated values too.

Re: ConsolidatedMax

Posted: Tue Apr 21, 2020 2:04 pm
by ascheevel
TM1NB wrote: ↑Tue Apr 21, 2020 8:28 am ...nasty in some ways. I guess your idea will go in the same direction.
Yes it certainly could. I never said it was efficient or elegant, only illustrated one possible solution.

Re: ConsolidatedMax

Posted: Thu Apr 23, 2020 1:19 pm
by mawitti
Hi TM1NB,

did you already try to use

Code: Select all

ConsolidateChildren(!WS_POS,!WS_Zeit,!WS_Kennzahl)
Markus

Re: ConsolidatedMax

Posted: Mon Apr 27, 2020 7:05 am
by TM1NB
Hi Markus,

thanks for your reply. Yes, my "solution" is a mix of both, consolidatedmax & consolidate children. Definitely very ugly, but it seems to work.

It's going something like this:

Code: Select all

IF(DTYPE( 'WS_POS', !WS_POS) @= 'C'												
& DTYPE('WS_Marke', !WS_Marke) @= 'N'	
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'	
& DTYPE('WS_Segment', !WS_Segment) @= 'N'	
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
consolidatedmax(2,'WS_Planung',!WS_Marke,!WS_Warengruppe,!WS_Segment,!WS_Option,!WS_Groesse,!WS_Channel,!WS_POS,!WS_Zeit,!WS_Plantyp,!WS_Version,!WS_Kennzahl,!WS_Wertfeld),

IF( DTYPE('WS_POS', !WS_POS) @= 'C'
& DTYPE('WS_Marke', !WS_Marke) @= 'C'
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'
& DTYPE('WS_Segment', !WS_Segment) @= 'N'
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
ConsolidateChildren('WS_Marke'),
.
.
.

Re: ConsolidatedMax

Posted: Mon Apr 27, 2020 7:39 am
by Wim Gielis
TM1NB wrote: ↑Mon Apr 27, 2020 7:05 am Hi Markus,

thanks for your reply. Yes, my "solution" is a mix of both, consolidatedmax & consolidate children. Definitely very ugly, but it seems to work.

It's going something like this:

Code: Select all

IF(DTYPE( 'WS_POS', !WS_POS) @= 'C'												
& DTYPE('WS_Marke', !WS_Marke) @= 'N'	
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'	
& DTYPE('WS_Segment', !WS_Segment) @= 'N'	
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
consolidatedmax(2,'WS_Planung',!WS_Marke,!WS_Warengruppe,!WS_Segment,!WS_Option,!WS_Groesse,!WS_Channel,!WS_POS,!WS_Zeit,!WS_Plantyp,!WS_Version,!WS_Kennzahl,!WS_Wertfeld),

IF( DTYPE('WS_POS', !WS_POS) @= 'C'
& DTYPE('WS_Marke', !WS_Marke) @= 'C'
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'
& DTYPE('WS_Segment', !WS_Segment) @= 'N'
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
ConsolidateChildren('WS_Marke'),
.
.
.
How is the performance ?

Re: ConsolidatedMax

Posted: Mon Apr 27, 2020 11:54 am
by HighKeys
Wim Gielis wrote: ↑Mon Apr 27, 2020 7:39 am
TM1NB wrote: ↑Mon Apr 27, 2020 7:05 am Hi Markus,

thanks for your reply. Yes, my "solution" is a mix of both, consolidatedmax & consolidate children. Definitely very ugly, but it seems to work.

It's going something like this:

Code: Select all

IF(DTYPE( 'WS_POS', !WS_POS) @= 'C'												
& DTYPE('WS_Marke', !WS_Marke) @= 'N'	
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'	
& DTYPE('WS_Segment', !WS_Segment) @= 'N'	
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
consolidatedmax(2,'WS_Planung',!WS_Marke,!WS_Warengruppe,!WS_Segment,!WS_Option,!WS_Groesse,!WS_Channel,!WS_POS,!WS_Zeit,!WS_Plantyp,!WS_Version,!WS_Kennzahl,!WS_Wertfeld),

IF( DTYPE('WS_POS', !WS_POS) @= 'C'
& DTYPE('WS_Marke', !WS_Marke) @= 'C'
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'
& DTYPE('WS_Segment', !WS_Segment) @= 'N'
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
ConsolidateChildren('WS_Marke'),
.
.
.
How is the performance ?
You should split your if Statement with continues for better performance.

TM1 Handled the IF condition like checking every condition inside even if the first on is already true, if you splitt it into multiple statements with Continue breaks in it, it should be much faster in processing.

BR

Re: ConsolidatedMax

Posted: Tue Apr 28, 2020 1:27 pm
by TM1NB
Hi,
HighKeys wrote: ↑Mon Apr 27, 2020 11:54 am
Wim Gielis wrote: ↑Mon Apr 27, 2020 7:39 am
TM1NB wrote: ↑Mon Apr 27, 2020 7:05 am Hi Markus,

thanks for your reply. Yes, my "solution" is a mix of both, consolidatedmax & consolidate children. Definitely very ugly, but it seems to work.

It's going something like this:

Code: Select all

IF(DTYPE( 'WS_POS', !WS_POS) @= 'C'												
& DTYPE('WS_Marke', !WS_Marke) @= 'N'	
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'	
& DTYPE('WS_Segment', !WS_Segment) @= 'N'	
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
consolidatedmax(2,'WS_Planung',!WS_Marke,!WS_Warengruppe,!WS_Segment,!WS_Option,!WS_Groesse,!WS_Channel,!WS_POS,!WS_Zeit,!WS_Plantyp,!WS_Version,!WS_Kennzahl,!WS_Wertfeld),

IF( DTYPE('WS_POS', !WS_POS) @= 'C'
& DTYPE('WS_Marke', !WS_Marke) @= 'C'
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'
& DTYPE('WS_Segment', !WS_Segment) @= 'N'
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
ConsolidateChildren('WS_Marke'),
.
.
.
How is the performance ?
You should split your if Statement with continues for better performance.

TM1 Handled the IF condition like checking every condition inside even if the first on is already true, if you splitt it into multiple statements with Continue breaks in it, it should be much faster in processing.

BR
performance is still good, but it can't be fast enough, right? =)

Your hint about the IF-statements sounds nice. So you would split it in more calculations like this?

Code: Select all

['x']=
IF(DTYPE( 'WS_POS', !WS_POS) @= 'C'												
& DTYPE('WS_Marke', !WS_Marke) @= 'N'	
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'	
& DTYPE('WS_Segment', !WS_Segment) @= 'N'	
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
consolidatedmax(2,'WS_Planung',!WS_Marke,!WS_Warengruppe,!WS_Segment,!WS_Option,!WS_Groesse,!WS_Channel,!WS_POS,!WS_Zeit,!WS_Plantyp,!WS_Version,!WS_Kennzahl,!WS_Wertfeld),
continue);

['x']=
IF( DTYPE('WS_POS', !WS_POS) @= 'C'
& DTYPE('WS_Marke', !WS_Marke) @= 'C'
& DTYPE('WS_Warengruppe', !WS_Warengruppe) @= 'N'
& DTYPE('WS_Segment', !WS_Segment) @= 'N'
& DTYPE('WS_Zeit', !WS_Zeit) @= 'N',
ConsolidateChildren('WS_Marke'),
continue);

Re: ConsolidatedMax

Posted: Tue Apr 28, 2020 3:33 pm
by lotsaram
It sounds a little counter-intuitive (and it is) as too many Continue statements is generally pretty bad for rule performance. But this looks like just such a case where splitting conditions could improve performance. The key is that each condition must be ordered top to bottom in the rule in the order of frequency of distribution. That is, the 90% case must be first not last. If you have this kind of frequency distribution with 1 case of 80-90% and 9 cases making up the remaining 10-20% then it is very much worth it to split up same area statements with Continue rather than nested IF. On the other hand if you have 10 cases each with approx. 10% each then not much point (except for making rule easier to read). Overall the effect might be marginal though as ConsolidateChildren is likelly to be by far the most expensive part of the calculation unless WS_Marke dimension is very small.

Here are some good articles on how to optimize rules. 7 tips to writing faster tm1 rules & using }StatsByRule to fine tune rule performance.

TM1 in general is very well optimized as far as numeric calculations goes. Boolean tests in general are the most expensive operation so you always should minimize IF tests as much as possible (of course not always possible). And wherever possible use a pure numerical expression over a Boolean evaluation.

Simple example: populating a flag measure based on a test of another cell whether null or not.
['Flag'] = N: IF( ['other measure'] <> 0, 1, 0 );
From performance perspective the following would be much better
['Flag'] = N: ABS( SIGN( ['other measure'] ) );
(except of course people may have a harder time interpreting such rules).
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited