Planning Analytics

SKIPCHECK;

['Weight'] = IF(ELLEV('Month',!Month)>0,
    ConsolidateChildren('Month'),
     ABS(SIGN(['Quantity'])));

['Quantity Supporting']=N:['Quantity'];
['Quantity']= C:IF(ELLEV('Month',!Month)>0,
    ['Quantity Supporting']\['Weight'],
                             CONTINUE);

FEEDERS;

['Quantity']=>['Weight'];
['Quantity']=>['Quantity Supporting'];

------------------------------
Jitesh Parmar
------------------------------

Hi Jitesh,

Maybe an MDX solution could work as an alternative to the rules.

There are a couple of gotchas like not being able to use CurrentMember to derive a calculated member in the same axis and you may need to get more creative using Generate() but here is something that could get you started.

May Sales cube has the following in 2023 Q1:

Notice some months have all 3 values, others 2 and others 1 value only.

WITH 
   MEMBER [Sales Measures].[Sales Measures].[Months_}QSM] AS
   COUNT(
      FILTER(
         [Period].[Period].CURRENTMEMBER.CHILDREN , [Sales Measures].[Sales Measures].[Units] <> 0
         )
     )
   MEMBER [Sales Measures].[Sales Measures].[Units AVG] AS ([Sales Measures].[Sales Measures].[Units] / 
        IIF([Sales Measures].[Sales Measures].[Months_}QSM] =0,
            1,
            [Sales Measures].[Sales Measures].[Months_}QSM] )
 ), SOLVE_ORDER = 1, FORMAT_STRING = '#,##0;(#,##0)' 
SELECT NON EMPTY 
   {
      [Sales Measures].[Sales Measures].[Units],
      [Sales Measures].[Sales Measures].[Units AVG]
   } ON 0, NON EMPTY 
   {
      TM1SubsetToSet([Product].[Product],"Default","public")
   } ON 1 
FROM
   [Sales] 
WHERE (
   [Scenario].[Scenario].[Actual], 
   [Customer].[Customer].[Total Customers], 
   [Period].[Period].[2023^2023-JAN])

The MDX code is taking the value of the Quarter (could be any level) then dividing by the number of child elements where Units is not zero, to only average based on populated months (children).

Note that I am using an intermediate Query Scoped Member to derive the number of periods and if we are at a leaf level or there are no children with values, we divide by 1 to avoid divide by zero errors.

Hope this gives you something to work with.

Original Message:
Sent: Sun March 16, 2025 08:47 AM
From: Jitesh Parmar
Subject: Consolidated Weighted Time Average Calcaution

Hi,

 

I am trying to calculate a weighted time average. My requirements are simple:

 

QuarterlyAVG = Sum of all months in the quarter / Quarter Weights

HalfYearAVG = Sum of all quarters in HY / HY Weights

FullYearAVG = Sum of half-years in FY / FY Weights

… and so on.

I initially attempted to achieve this using a single formula with the ConsolidateChildren and ConsolidatedAVG functions. However, this approach did not work as expected, as ConsolidatedAVG calculates the average based on all leaf-level elements rather than their immediate children.

 

My current approach is as follows:

My primary measure element is 'Quantity'. To support the weighted average calculation, I created two additional elements: 'Weight' and 'Quantity Supporting'.

I have implemented the necessary rules and feeders to perform the calculations,

SKIPCHECK;['Weight'] = IF(ELLEV('Month',!Month)>0,    ConsolidateChildren('Month'),     ABS(SIGN(['Quantity'])));['Quantity Supporting']=N:['Quantity'];['Quantity']= C:IF(ELLEV('Month',!Month)>0,    ['Quantity Supporting']\['Weight'],                             CONTINUE);FEEDERS;['Quantity']=>['Weight'];['Quantity']=>['Quantity Supporting'];

and the screenshot below shows the final results.

 

Challenges with this approach are it introduces unnecessary data duplication, supporting calculations make it highly inefficient and for each measure requiring an average calculation, I need to create two additional elements, which adds complexity to maintenance.

One improvement could be to make 'Quantity Supporting' a parent of 'Quantity' and reduce some feeder and rule, but I am looking for a more efficient and optimized approach to achieve this calculation without redundancy.

 

If anyone has a better solution or alternative method, I would appreciate your insights.

 

Thanks!

Regards,

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Regards,

Jitesh

Original Message:
Sent: Sun March 16, 2025 03:04 PM
From: George Tonkin
Subject: Consolidated Weighted Time Average Calcaution

Hi Jitesh,

Maybe an MDX solution could work as an alternative to the rules.

There are a couple of gotchas like not being able to use CurrentMember to derive a calculated member in the same axis and you may need to get more creative using Generate() but here is something that could get you started.

May Sales cube has the following in 2023 Q1:

Notice some months have all 3 values, others 2 and others 1 value only.

WITH    MEMBER [Sales Measures].[Sales Measures].[Months_}QSM] AS   COUNT(      FILTER(         [Period].[Period].CURRENTMEMBER.CHILDREN , [Sales Measures].[Sales Measures].[Units] <> 0         )     )   MEMBER [Sales Measures].[Sales Measures].[Units AVG] AS ([Sales Measures].[Sales Measures].[Units] /         IIF([Sales Measures].[Sales Measures].[Months_}QSM] =0,            1,            [Sales Measures].[Sales Measures].[Months_}QSM] ) ), SOLVE_ORDER = 1, FORMAT_STRING = '#,##0;(#,##0)' SELECT NON EMPTY    {      [Sales Measures].[Sales Measures].[Units],      [Sales Measures].[Sales Measures].[Units AVG]   } ON 0, NON EMPTY    {      TM1SubsetToSet([Product].[Product],"Default","public")   } ON 1 FROM   [Sales] WHERE (   [Scenario].[Scenario].[Actual],    [Customer].[Customer].[Total Customers],    [Period].[Period].[2023^2023-JAN])

The MDX code is taking the value of the Quarter (could be any level) then dividing by the number of child elements where Units is not zero, to only average based on populated months (children).

Note that I am using an intermediate Query Scoped Member to derive the number of periods and if we are at a leaf level or there are no children with values, we divide by 1 to avoid divide by zero errors.

Hope this gives you something to work with.

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Sun March 16, 2025 08:47 AM
From: Jitesh Parmar
Subject: Consolidated Weighted Time Average Calcaution

Hi,

 

I am trying to calculate a weighted time average. My requirements are simple:

 

QuarterlyAVG = Sum of all months in the quarter / Quarter Weights

HalfYearAVG = Sum of all quarters in HY / HY Weights

FullYearAVG = Sum of half-years in FY / FY Weights

… and so on.

I initially attempted to achieve this using a single formula with the ConsolidateChildren and ConsolidatedAVG functions. However, this approach did not work as expected, as ConsolidatedAVG calculates the average based on all leaf-level elements rather than their immediate children.

 

My current approach is as follows:

My primary measure element is 'Quantity'. To support the weighted average calculation, I created two additional elements: 'Weight' and 'Quantity Supporting'.

I have implemented the necessary rules and feeders to perform the calculations,

SKIPCHECK;['Weight'] = IF(ELLEV('Month',!Month)>0,    ConsolidateChildren('Month'),     ABS(SIGN(['Quantity'])));['Quantity Supporting']=N:['Quantity'];['Quantity']= C:IF(ELLEV('Month',!Month)>0,    ['Quantity Supporting']\['Weight'],                             CONTINUE);FEEDERS;['Quantity']=>['Weight'];['Quantity']=>['Quantity Supporting'];

and the screenshot below shows the final results.

 

Challenges with this approach are it introduces unnecessary data duplication, supporting calculations make it highly inefficient and for each measure requiring an average calculation, I need to create two additional elements, which adds complexity to maintenance.

One improvement could be to make 'Quantity Supporting' a parent of 'Quantity' and reduce some feeder and rule, but I am looking for a more efficient and optimized approach to achieve this calculation without redundancy.

 

If anyone has a better solution or alternative method, I would appreciate your insights.

 

Thanks!

Regards,

Jitesh

------------------------------
Jitesh Parmar
------------------------------

DOS: HY NOT HalfYearAvg = Year * Sum;

Year * Sum = 2;

Solve

Original Message:
Sent: Sun March 16, 2025 08:47 AM
From: Jitesh Parmar
Subject: Consolidated Weighted Time Average Calcaution

Hi,

 

I am trying to calculate a weighted time average. My requirements are simple:

 

QuarterlyAVG = Sum of all months in the quarter / Quarter Weights

HalfYearAVG = Sum of all quarters in HY / HY Weights

FullYearAVG = Sum of half-years in FY / FY Weights

… and so on.

I initially attempted to achieve this using a single formula with the ConsolidateChildren and ConsolidatedAVG functions. However, this approach did not work as expected, as ConsolidatedAVG calculates the average based on all leaf-level elements rather than their immediate children.

 

My current approach is as follows:

My primary measure element is 'Quantity'. To support the weighted average calculation, I created two additional elements: 'Weight' and 'Quantity Supporting'.

I have implemented the necessary rules and feeders to perform the calculations,

SKIPCHECK;['Weight'] = IF(ELLEV('Month',!Month)>0,    ConsolidateChildren('Month'),     ABS(SIGN(['Quantity'])));['Quantity Supporting']=N:['Quantity'];['Quantity']= C:IF(ELLEV('Month',!Month)>0,    ['Quantity Supporting']\['Weight'],                             CONTINUE);FEEDERS;['Quantity']=>['Weight'];['Quantity']=>['Quantity Supporting'];

and the screenshot below shows the final results.

 

Challenges with this approach are it introduces unnecessary data duplication, supporting calculations make it highly inefficient and for each measure requiring an average calculation, I need to create two additional elements, which adds complexity to maintenance.

One improvement could be to make 'Quantity Supporting' a parent of 'Quantity' and reduce some feeder and rule, but I am looking for a more efficient and optimized approach to achieve this calculation without redundancy.

 

If anyone has a better solution or alternative method, I would appreciate your insights.

 

Thanks!

Regards,

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hi,

Would it be inconvenient to populate weight via a TI and then use YTD consolidations in your Month dimension for the calculation?

In other words have another rollup in the Month dimension, and in the rule refer to the relevant YTD element.

regards,

Mark

Original Message:
Sent: Sun March 16, 2025 08:47 AM
From: Jitesh Parmar
Subject: Consolidated Weighted Time Average Calcaution

Hi,

 

I am trying to calculate a weighted time average. My requirements are simple:

 

QuarterlyAVG = Sum of all months in the quarter / Quarter Weights

HalfYearAVG = Sum of all quarters in HY / HY Weights

FullYearAVG = Sum of half-years in FY / FY Weights

… and so on.

I initially attempted to achieve this using a single formula with the ConsolidateChildren and ConsolidatedAVG functions. However, this approach did not work as expected, as ConsolidatedAVG calculates the average based on all leaf-level elements rather than their immediate children.

 

My current approach is as follows:

My primary measure element is 'Quantity'. To support the weighted average calculation, I created two additional elements: 'Weight' and 'Quantity Supporting'.

I have implemented the necessary rules and feeders to perform the calculations,

SKIPCHECK;['Weight'] = IF(ELLEV('Month',!Month)>0,    ConsolidateChildren('Month'),     ABS(SIGN(['Quantity'])));['Quantity Supporting']=N:['Quantity'];['Quantity']= C:IF(ELLEV('Month',!Month)>0,    ['Quantity Supporting']\['Weight'],                             CONTINUE);FEEDERS;['Quantity']=>['Weight'];['Quantity']=>['Quantity Supporting'];

and the screenshot below shows the final results.

 

Challenges with this approach are it introduces unnecessary data duplication, supporting calculations make it highly inefficient and for each measure requiring an average calculation, I need to create two additional elements, which adds complexity to maintenance.

One improvement could be to make 'Quantity Supporting' a parent of 'Quantity' and reduce some feeder and rule, but I am looking for a more efficient and optimized approach to achieve this calculation without redundancy.

 

If anyone has a better solution or alternative method, I would appreciate your insights.

 

Thanks!

Regards,

Jitesh

------------------------------
Jitesh Parmar
------------------------------