Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  Consolidated Weighted Time Average Calcaution

    Posted 03/16/25 08:47 AM
    Edited by Jitesh Parmar 03/16/25 08:48 AM
    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
    ------------------------------



  • 2.  RE: Consolidated Weighted Time Average Calcaution

    Posted 03/16/25 03:05 PM

    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
    ------------------------------



  • 3.  RE: Consolidated Weighted Time Average Calcaution

    Posted 03/17/25 01:52 PM
    Hi George,
     
    Thanks for taking the time to share this post.
     
    It's a fantastic idea, and I really like it! I can see MDX doing wonders in this scenario. :-)
     
    Unfortunately, it won't work for my case, as the customer is still using an Architect/Perspectives setup, and these values need to be captured and stored in the cube for further calculations.
     
    Thanks again!


    Regards,

    Jitesh





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

    Original Message


  • 4.  RE: Consolidated Weighted Time Average Calcaution

    Posted 03/18/25 11:54 AM

    DOS: HY NOT HalfYearAvg = Year * Sum;

    Year * Sum = 2;

    Solve



    ------------------------------
    J Hansen Esq
    ------------------------------



  • 5.  RE: Consolidated Weighted Time Average Calcaution

    Posted 03/21/25 08:52 AM

    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



    ------------------------------
    Mark Wragg
    ------------------------------



Related Content