TM1 Forum

Hello all

I have a need to create an Active form, whereby the (only) row dimension will not be part of the cube in the active form.
Specifically, the dimension is in no cube at all, but is a concatenation of 3 dimensions in the current cube.
In that dimension, I have an MDX expression for a top 10 count.

As far as I know, the top 10 count can not be done on 3 dimensions in the same cube.
So, the 10 best combinations of 3 dimensions - model / quality / colour in a cube to analyze sales of clothes.
Therefore, I created a different dimension to hold the concatenated items and order by sales quantity. Get the 10 best combinations in an MDX, and use that one in the active form.

Mid() functions in Excel can then take out the 3 parts (or I could use attributes, does not matter in the end).

Any help from you?
Thanks in advance!

Wim

I haven't used topcount myself for anything serious/production, but I was under the impression that it was a filter against a measure. So, wouldn't topcount automatically select the highest resulting combinations of each of your target dims? I'm not following why you need to create the concatenated dimension. If you do it your way, also remember that the report will not be fully dynamic-- it'll only be as current as the last time you recreated the dim.

That said, if it's working and you're happy with it you can do what you describe as long as every element in each original dim is a constant length. If not, you need to pick a separator and use FIND or SEARCH instead of MID.

Matt

Hello Matt

Thank you for replying.

I have a cube, of which 3 dimensions need to produce the top count.
It's not the topcount over elements of 1 dimension, where we take the top consolidation in the other 2 dimensions.
The concatenation of 3 elements produces a data point each time.

So I create a temporary dimension holding the concatenated elements. And a numeric attribute for the value.
After that, an MDX to do the topcount. It is correct and gives me the correct 10 elements, sorted on the numeric attribute.

That subset or MDX expression, I would like to use as members in the rows. With MID, LEFT, RIGHT to get the original members and working DBRW formulas.
There is an action button to execute a process that creates the new dmension, it's very fast.
But it's giving me nothing in the rows of the active form.

So, does anyone know, should the dimension(s) in the rows be part of the "main" cube for the active form?

Thanks.

Wigi

Wim,
I've had to exactly as you describe for the same reasons.
The only additional step you need to do is to make your active form against the }ElementAttributes cube of your temporary dimension.

The MID functions (or DBRAs) could then split out your individual dimension elements, which could be used by DBRWs back to the main cube.
These DBRWs would not reference the Active Form TM1RPTVIEW cube reference. i.e. are just normal DBRWs, not part of the Active Form.

Worked a treat for me.

Paul

Hi Wim

Maybe I didn't understand exactly what you are trying to do but
1/ active form report rows must be from the cube that the view in the TM1rptview formula builds
2/ TM1 and maybe MDX in general has the limitation that a filter like topcount is only valid on a view with a single row dimension
... So I don't understand how your solution as described would work.

I would have thought that to build a second cube using not the original 3 dimensions but the concatenated dimension would be the most garunteed and maybe even the simplest solution. The second cube could be "virtual" and 100% rule values if there is a requirement for the topcounts to be dynamically updatable.

Thank you all. I will respond in the coming days (I will not return to the customer with this problem the coming weeks, only in August).
So no pressure at all.

Wim