Business Rule Max of sons of a parent

[nicola531]

Hello everybody,

I've got an issue like this:

I've got 2 dimension

Dimension objects in (hierarchy)
Dimension date

A cube with those two dimension and a view like this:
Start Date
Object A 13/10/2009
Obejct A1 12/10/2009
Obejct A2 09/10/2009
Obejct A3 13/10/2009


I would like to know if there is a business rule that allow for the element father Object A to have in that cross (ObjectA, Start Date) the Max date value of his sons.
For the time being I know that there is just MAX or MIN function that runs the comparison between to element but not in a subset on N elment.

Awating for answers
Thanks for you time.

N.D.

[Jeroen Eynikel]

I think you will have to use TI to calculate this. (If Iunderstand your question correctly)

Maybe it could be done using MDX as well. I am not sure however.

[Wim Gielis]

I, too, would say that you need TI. Meaning that this is not a dynamic solution, you have to execute the TI process. However, if the dates are loaded with a TI process, I would insert the computation of the MAX date in the TI process. Shouldn't be too hard. Each time you insert a date, check whether it's larger than it's immediate parent (hope the Objects dimension does not have a difficult structure). If it's larger, the parent should have the higher value too.

But then again, how are we going to write data on the C level in a TI process

At first sight, I would use a second measure to store at the N level the MAX date for on let's say one of the children of the consolidation. The other cells are zero. Then by default the consolidated level for the second measure shows the correct 'total'. A rule could then copy over the consolidated total to the first measure. Will work, but not the nicest solution IMO.

I bet there must be better solutions...

[Gregor Koch]

Hi

Recently looked into this (not string dates) as well and came up with a TI solution.

But...

But then again, how are we going to write data on the C level in a TI process

In this case writing to the consolidation is not a problem as it is a string measure.

At first sight, I would use a second measure to store at the N level the MAX date for on let's say one of the children of the consolidation. The other cells are zero. Then by default the consolidated level for the second measure shows the correct 'total'. A rule could then copy over the consolidated total to the first measure. Will work, but not the nicest solution IMO.

For a pure string date (of format 13/10/2009) this wouldn't work with out doing some conversions to numeric values in the first place, as the string value would not consolidate.

There are some other posts on the MAX of children here but I am still looking for a good/workable rule solution myself.

Cheers

[Wim Gielis]

Hmmm, I would not work with string dates, don't know why exactly though. I prefer (formatted) numbers.

For a pure string date (of format 13/10/2009) this wouldn't work with out doing some conversions to numeric values in the first place, as the string value would not consolidate.

In fact, I would store the date as a number, and change the formatting attribute to be a date.

Nicola: if applicable, don't forget to account for the difference in "day 1" between Excel (1/1/1900) and TM1 (1/1/1960).

Wim