ConsolidatedMax
-
- Posts: 18
- Joined: Thu Jan 02, 2020 3:05 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 365
ConsolidatedMax
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!
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!
-
- Posts: 18
- Joined: Thu Jan 02, 2020 3:05 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 365
Re: ConsolidatedMax
No one? No ideas or hints?
-
- Community Contributor
- Posts: 287
- Joined: Fri Feb 15, 2013 5:49 pm
- OLAP Product: TM1
- Version: PA 2.0.9.1
- Excel Version: 365
- Location: Minneapolis, USA
Re: ConsolidatedMax
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.
- Attachments
-
- ConsolidatedMax_example_20200420.jpg (115.04 KiB) Viewed 5515 times
-
- Posts: 18
- Joined: Thu Jan 02, 2020 3:05 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 365
Re: ConsolidatedMax
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.
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.
-
- Posts: 11
- Joined: Mon Apr 06, 2020 2:08 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 11.2
- Excel Version: Office 365 -V16
- Location: Germany
Re: ConsolidatedMax
Hi TM1NB,
did you already try to use
Markus
did you already try to use
Code: Select all
ConsolidateChildren(!WS_POS,!WS_Zeit,!WS_Kennzahl)
-
- Posts: 18
- Joined: Thu Jan 02, 2020 3:05 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 365
Re: ConsolidatedMax
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:
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'),
.
.
.
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: ConsolidatedMax
How is the performance ?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'), . . .
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedMax
You should split your if Statement with continues for better performance.Wim Gielis wrote: ↑Mon Apr 27, 2020 7:39 amHow is the performance ?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'), . . .
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
-
- Posts: 18
- Joined: Thu Jan 02, 2020 3:05 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 365
Re: ConsolidatedMax
Hi,
Your hint about the IF-statements sounds nice. So you would split it in more calculations like this?
performance is still good, but it can't be fast enough, right? =)HighKeys wrote: ↑Mon Apr 27, 2020 11:54 amYou should split your if Statement with continues for better performance.Wim Gielis wrote: ↑Mon Apr 27, 2020 7:39 amHow is the performance ?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'), . . .
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
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);
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: ConsolidatedMax
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).
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).
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.