Can you use ConsolidatedCountUnique with criteria

[M1ndbender]

I am wondering if you can use ConsolidatedCountUnique with a "where" type clause in a TM1 rule.

I am trying to customize the way the consolidation work.

As you see in the attachment, at the level location everything is rolling up as I would like. If the location had volume for a day in the month then at the month level they would have a 1 etc.

The issue is when the locations start to be consolidated. I am trying to get it to go horizontal at this point. In the attachment you will see the 4 locations roll up to NB012. 3 of then had volume (indicated by the 1) and one did not (indicated by the 0)

I am trying to get the rollup value to be 3 (summing the ones with a 1)

I thought I could use ConsolidateCountUNique and add something to only count the 1s but right now it is doing what it is supposed to and counting all 4.

Any help would be appreciated

test.png (15.56 KiB) Viewed 391 times

[MarenC]

Hi,

I thought I could use ConsolidateCountUNique and add something to only count the 1s but right now it is doing what it is supposed to and counting all 4.

I assume you meant to say not doing what it is supposed to do?

Anyhows I seem to get this to work, it would therefore help if you showed us the rule!

Maren.

[Steve Rowe]

Hi,
I'd be very careful about using the Consolidate.... family of functions. They do some strange things to the calculation engine including (I believe / been told) turn off the sparse data calculation methods (skipcheck / feeders) "underneath" them. They can lead to significant RAM growth and slow performance. (Happy to be wrong here!)

More generally changing the behaviour at the C level of the cube away from "just adding up" very often ends up with problems further down the road as you have to deal with the directionality of the calculations. This is what you are hitting now with the engine calculating the 4 by adding down the page rather than across. This could be fixed by use of ConsolidateChildren but I really wouldn't recommend that you keep digging in this hole...

So in short don't do it this way if you can avoid it.

I'd be thinking about if I need a second cube that is dimensioned by period only rather than by day. Then I can write a rule that says

Site Count in Period Only Cube = If (Site Count in Day cube > 0 then 1 else 0)

or create a new measure in the Day Cube called Site Count Period and then in the TI (that I assume is populating the base Site Counts) put a 1 in the intersection of "Site Count Period / Day 1 of the period" everytime I write a populated flag to the cube.

The key here is that you need the Site Count for the period to be an N level item so that it can add up naturally.

HTH