Sum of calculated children members

[bruno.zanelli]

Hello, guys!

I'm new to this forum and to TM1 and I already have a project that needs a difficult calculation (at least for me).

There are 4 dimensions: Period, Accounts, Organization and BU. Attached, there's an example of Periods dimension structure.

Periodos.png (4.99 KiB) Viewed 5790 times

This calculation gives me the monthly grades for all organizations. Data is loaded monthly, for the first day of each month.

In Accounts, there are:

qtde_meta_visitas
qtde_meta_contatos
qtde_visitas_feitas
qtde_contatos_feitos
nota_%_rm

By this, i've built the following calculation, wich results in a percentual value:

['nota_%_rm'] = (((['qtde_visitas_feitas'] / ['qtde_meta_visitas']) + (['qtde_contatos_feitos'] / ['qtde_meta_contatos']))/2);

My business rule requires that this calculation works only for leaf members of Organization dimension. For consolidated levels, it should be the average value ("sum of children grades" / "number of children")

I'm not able to sum the grades, as per default TM1 is calculating it in all levels, for all dimensions.

Hope you can help! Thanks a lot!!!

[Steve Rowe]

Welcome!

['nota_%_rm'] = N: (((['qtde_visitas_feitas'] / ['qtde_meta_visitas']) + (['qtde_contatos_feitos'] / ['qtde_meta_contatos']))/2);
['nota_%_rm'] = C: ("sum of children grades" / "number of children");

Sum of children is the harder part but there are plenty of threds on calculating averages.
The N and C above is how you qualify were you want the rule to apply.

Wait I think you mean children as in people type children so that might not be an issue. We also children to refer to N levels in parent / child type terminolgy.... Anyway I'm probably just confusing the issue.

Use N and C and do a search on the forum for averages if you are still stuck....

[bruno.zanelli]

Steve, I guess I didn't make myself clear about all problems involved in this issue! I'm really sorry!

The point is that i'm not able to use this train of thought, because I just want this calculation (['nota_%_rm'] = C: ("sum of children grades" / "number of children");) to work on Organization dimension and and 'nota_%_rm' is a calculated member.

I already tried your sugestion, but it doesnn't behave as expected for consolidated members.

Resuming, I want the consolidated calculation for 'nota_%_rm' to work only on Organization dimension.

By "children" I mean the dimensional terminology, as parent, ancestor, descendant.

Thanks a lot for your answer!!!!!

[garry cook]

If you only want a rule to operate at a consolidated level for one dimension, just use ['nota_%_rm'] = C: IF(ELLEV('Organization', !Organization)>0, ("sum of children grades" / "number of children"), CONTINUE); syntax.

Apologies if this isn't what you meant.

[bruno.zanelli]

I got your ideas, but i'm having problems on "sum of children grades" across Organization dimension (only). Is there a function not listed in developer guide or some works around for this?

eg:

Org1 - sum(Org1.1 + Org1.2)
Org1.1 - sum(Org1.1.1 + Org1.1.2 + Org1.1.3)
Org1.1.1
Org1.1.2
Org1.1.3
Org1.2 - sum(Org1.2.1 + Org1.2.2 + Org1.2.3)
Org1.2.1
Org1.2.2
Org1.2.3

What happens in my case, is that rules summarize all dimensions.

Thanks a lot

[Gregor Koch]

Hi Bruno

I think you'll get their quicker if you make a screenshot of a cube view with values and the measures and different levels in question. And based on that explain where you want to see what.
For me it is not quite obvious how the tips you got from the others are not able to solve your problem.
Cheers