ConsolidatedMax

TM1NB · Post by **TM1NB** » Wed Apr 15, 2020 6:12 pm

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:

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!

TM1NB · Post by **TM1NB** » Mon Apr 20, 2020 2:02 pm

No one? No ideas or hints?

ascheevel · Post by **ascheevel** » Mon Apr 20, 2020 3:24 pm

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.

TM1NB · Post by **TM1NB** » Tue Apr 21, 2020 8:28 am

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.

ascheevel · Post by **ascheevel** » Tue Apr 21, 2020 2:04 pm

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.

mawitti · Post by **mawitti** » Thu Apr 23, 2020 1:19 pm

Hi TM1NB,

did you already try to use

Code: Select all

ConsolidateChildren(!WS_POS,!WS_Zeit,!WS_Kennzahl)

Markus

TM1NB · Post by **TM1NB** » 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'),
.
.
.

Wim Gielis · Post by **Wim Gielis** » 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 ?

HighKeys · Post by **HighKeys** » 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

TM1NB · Post by **TM1NB** » Tue Apr 28, 2020 1:27 pm

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);

lotsaram · Post by **lotsaram** » Tue Apr 28, 2020 3:33 pm

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).

TM1 Forum

ConsolidatedMax

ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax

Re: ConsolidatedMax