Changing formula for consolidation to sum rows

[ssp]

Please see attached file.

I have the following scenario where I calculate the daily average as 'Quantity\Level Number' at a 'C' level across all consolidations in the "Dim_Time4" dimension that begin with "L", (using a STET) however at the "Total Days" level I need the "Daily Average" to be the Sum of all daily averages by "L" consolidations.

So, currently, my rule is : ['Daily Avg']=C:IF(!Dim_Time4 @='Total Days', STET, DB('CubeName', !Dim_Time4, 'Quantity')\DB('CubeName', !Dim_Time4, 'Level Number'));

So, in my example, the intersection of "Total Days" & "Daily Average" should equate to 16.34. I'm guessing to use the ConsolidateChildren function?
How would I use it here?

[Mike L]

<response deleted> I misread the question.

[Mike L]

Ok, had a cuppa coffe and got my eyes open. Give this a try:

Code: Select all

['Daily Avg']=C:IF(!Dim_Time4 @='Total Days', STET, DB('CubeName', !Dim_Time4, 'Quantity')\DB('CubeName', !Dim_Time4, 'Level Number'));
['Daily Avg']=ConsolidateChildren('Dim_Time4');

But note that ConsolidateChildren is reported to have an adverse impact on performance, although that may only have been in version 8. Maybe the following would be faster, but I don't really know:

Code: Select all

['Daily Avg']=C:IF(!Dim_Time4 @='Total Days', STET, DB('CubeName', !Dim_Time4, 'Quantity')\DB('CubeName', !Dim_Time4, 'Level Number'));
['Total Days', 'Daily Avg']=ConsolidateChildren('Dim_Time4');

[ssp]

How about if I wanted the intersections of ['top', 'maxdate'] and ['rollingdays', 'maxdate'] not to aggregate, however to be equal to the value of '108245', which represents a date timestamp. Any suggestions?

[Mike L]

You can override these consolidations with a hard-coded constant by putting these statements before any other rules that would overlap the same area. (When evaluating a cell, the rule evaluator stops at the first rule it finds applicable to that cell.)

Code: Select all

['top', 'maxdate']=C:108245
['rollingdays', 'maxdate']=C:108245

But note, it is usually best to put datestamps and other variable information in an N element where the data can be loaded easily, rather than having to edit the rule every time you need to update the datestamp. (I have been known to use day number zero for this purpose.) If you still want to display it at the top of the hierarchy then the C level rule would use a reference to that N element.

[ssp]

Hi Mike,
I can't use datestamp or a hardcoded formula for this as the MAXDATE is different for each SKU. So in this cube there is another dimension, which determines the MAXDATE by SKU. Essentially, the MAXDATE is the last date in the range of days for which there was a sale. So I need a formula, which at the consoldiated level takes the value of the MAXDATE from the N level without consolidating for each SKU.

So, for example, SKU1 might have a MAXDATE of 108245, SKU2 might have MAXDATE of 108333.

[Mike L]

Aah, the messes we get into when mixing data with different dimensionality in one cube! [Hint]

Assuming (silly assumption) Dim_Time3 is not more complex than pictured, and that the day '1' always has its MaxDate populated with the desired value, this could be something as simple as:

Code: Select all

['rollingdays', 'maxdate']=C:['1', 'maxdate'];
['top', 'maxdate']=C:['rollingdays', 'maxdate'];

Otherwise, you need to explain a bit more about the schema.

[ssp]

i guess I could create a separate two dimensional cube, SKU by MAXDATE.

However, I think with what you've got there and together with some of the logic I have I've got the answer to keep it in the one cube. This cube is not going to be used for reporting or any analysis. It is just a staging cube to store sales by maxdate with few other calculations. Other cubes in my application use this information to create reporting cubes for statistical analysis.

Thanks for your help.

[ssp]

What if the consolidatechildren had to work such that it consolidates different children or range of children depending on the SKU?

So for SKU1 it consolidates children 1..3, SKU2 it consolidates children 1..20. Can this be achieved with this function?

[Mike L]

What if the consolidatechildren had to work such that it consolidates different children or range of children depending on the SKU?

So for SKU1 it consolidates children 1..3, SKU2 it consolidates children 1..20. Can this be achieved with this function?

No. In the target area on the left you can specify where things consolidate into but on the right, i.e. the parameter of the ConsolidateChildren command, you can only specify the whole dimension. It uses the consolidations that are defined in the dimension.

[ssp]

So if you take at a look at my first screenshot in this thread. Say I had another dimension called SKU. Say, there were two SKUs, 1 & 2.

I want SKU 1 "daily avg" to be equal to the summation of consolidations: L1 D108233, L2 D108233, L3 D108333
but want SKU 2 "daily avg" to be equal to the summation of consolidations: L1 D108233, L2 D108233, L3 D108333, L4 D108233, L5 D108233

Is this possible?

[Steve Rowe]

Not using consolidate children it isn't.

Difficult to give you too much idea with out knowing a bit more about the underlying functionality you are trying to achieve.

I'd be looking for another couple of measures against the SKU that I can use to test against or use in my calculation.

Something like

['Quantity to Use'] = If ( ['Use this']>0 , ['Quantity'], 0);

Hopefully you get the idea, the trick of course is to figure a way of calculating the 'Use This' flag. Once you have 'Quantity to Use' you can drive the average calc from this.

Just a note of caution, when things get this fiddly and complex it's often a sign that you have your underlying desgin wrong. You may, as has already been suggested, be mixing data that doesn't belong together (i.e. differently dimensioned, this applies to the calculations as well as the rules.) or trying to do too many of your calcualtions in the same cube. C level rules are always a problem in TM1 and for me personally having to do more than 1 or 2 in cube indicates to me that I have a design issue that I need to resolve. If I was trying to resolve the issues that it looks like you are I'd be looking at the desgin and try and solve them there rather than in rules.

Cheers