SUMIF function in Rules?

[DevGreg]

Dear all,

Do you know if there's an equivalent in TM1 rules of the Excel SUMIF function?
Doesn't seem to be in the documentation but maybe it's not complete / you have a way to do this another way.

Here's what I'd like to achieve: in my "Events" dimension, the user will have the possibility to create new elements via TI.
He will then be able to put values for each of them, and assign a "maturity level" through a string-type measure: "mature" / "not mature".

I would like that depending on this maturity level, the costs allocated to all events created be aggregated in consolidations that are above, for example:

Code: Select all

Total Mature           100      ------
Total Not Mature        50        ------
All 2012 Events
 - Event 1               50        Mature
 - Event 2               30        Not Mature
 - Event 3               20        Not Mature
 - Event 4               50        Mature

I don't want events to go directly under the consolidations "Total Mature" / "Total Not Mature", and I would appreciate that calculation are executed in real time (no TI if possible).

Thanks in advance for your feedback.

Kind regards,

Greg

[Christopher Kernahan]

Greg,

Create two additional measures, and hide these from the users.

In Measures, have;

Measure
system_mature_m
system_not_mature_m

Your rule is then;

[ 'system_mature_m' ] = N:
IF( DB( 'Cube', !Events, 'Measure') @= 'Mature',
DB( 'Cube', !Events, 'Measure'), 0);

[ 'system_not_mature_m' ] = N:
IF( DB( 'Cube', !Events, 'Measure') @= 'Not Mature',
DB( 'Cube', !Events, 'Measure'), 0);

[ 'Total Mature' ] = C:
DB( 'Cube', 'Total Mature', 'system_mature_m');

[ 'Total Not Mature' ] = C:
DB( 'Cube', 'Total Not Mature', 'system_not_mature_m');

In the C rule above you could really refer to either Total Mature or Total Not Mature as these will have the same value, so to make it cleaner you might want to create a system_maturity_sum consolidation and use that instead.

[Kaveenga]

An approach could be to have extra measures in the cube for each maturity level, which you can rule across the value depending on the maturity type selected by the user.

[DevGreg]

Nice workaround, thank you guys!

I may encounter some issues as there are 3 different cost data entry (for 3 currencies, modelized with 3 measures), but this was just the help I needed to make me think "out of the box".

[Christopher Kernahan]

Hi Greg,

If your events are unique to each time period i.e. Event1 appears only in 2012 and no other, then you also have the option of using attributes in place of measures.

I'm not sure which is best for your setup but thought I would add this in.

Kaveenga,

Just got in front!

[DevGreg]

Hello Christopher,

Attributes are not a good solution for me because I want to archive (through another dimension) the status of my events during time.
That's why I didn't choose to place the events directly below the consolidations "Mature" / "Not Mature", as the maturity level will change through time and I need to be able to rebuild the story.

Thanks anyway for the hint

Regards,

Greg