TM1 Forum

Posted: **Wed Jun 21, 2023 12:13 pm**

I have a dynamic subset that is defined in the set editor using MDX, and I have created a subset calculation in a cube view to aggregate over this subset. If I click on the MDX View, I can see the code that has been used to create the calculation.

For reference, the subset definition is

Code: Select all

EXCEPT(FILTER(DRILLDOWNMEMBER({[ABC_BudgetLine].[ABC_BudgetLine].[Budget Line TOTAL]} , {[ABC_BudgetLine].[ABC_BudgetLine].[Budget Line TOTAL]}) , [ABC_c_ActivityAssignment].[ABC_c_ActivityAssignment].[Driver Missing] <> "Y") , {[ABC_BudgetLine].[ABC_BudgetLine].[Budget Line TOTAL]})

and the calculation at the start of the MDX View is

Code: Select all

WITH MEMBER [ABC_BudgetLine].[ABC_BudgetLine].[Driver Assigned(Sum)] AS SUM({DISTINCT({EXCEPT(FILTER(DRILLDOWNMEMBER({[ABC_BudgetLine].[ABC_BudgetLine].[Budget Line TOTAL]} , {[ABC_BudgetLine].[ABC_BudgetLine].[Budget Line TOTAL]}) , [ABC_c_ActivityAssignment].[ABC_c_ActivityAssignment].[Driver Missing] <> "Y") , {[ABC_BudgetLine].[ABC_BudgetLine].[Budget Line TOTAL]})})}), SOLVE_ORDER = 1, FORMAT_STRING = '#,##0.00;(#,##0.00)'

Is there any way that I can replicate this calculation using a user-defined consolidation in the rules file? I want to be able to pick up the calculated value and reference it in another cube.

(Google has told me that custom rollups are a thing in other IBM packages, but as far as I can see they still don't exist in PAW. My backup plan is to create a process that iterates through the elements in the subset and calculates the total through repeated use of CellIncrementN, but it feels like a bit of a faff for what is essentially a quick data validation check.)

Thanks in advance...

Posted: **Wed Jun 21, 2023 4:29 pm**

Rather than looping through the elements in the subset in a ti process and incrementing, you could use the mdx view you already have as the source for the process so the values are already aggregated.

To do this solely in rules Cellvaluen may be an option, I believe you can refer to a subset in place of an element name but I don't imagine the performance will be that great

Posted: **Wed Jun 21, 2023 5:16 pm**

Hi,

I was thinking that maybe you could create a consolidation based on the filter, i.e.,

Code: Select all

[ABC_c_ActivityAssignment].[ABC_c_ActivityAssignment].[Driver Missing] <> "Y"

and then build the rule using the consolidation.

But I am a bit confused that "ABC_c_ActivityAssignment" is a different dimension to "ABC_BudgetLine". Are they identical?

Maren

Posted: **Thu Jun 22, 2023 9:36 pm**

Hi both, thank you for your replies. I meant to come back to this today but nothing went to plan. And I'm now on leave until July so I will need to hold that thought for ten days...

TM1 Forum

consolidating a dynamic subset via the rules file

consolidating a dynamic subset via the rules file

Re: consolidating a dynamic subset via the rules file

Re: consolidating a dynamic subset via the rules file

Re: consolidating a dynamic subset via the rules file