Planning

Get Involved. Join the Conversation.

Topic

    Jash Joshi
    Has anyone used @CalcMgrExcelSUMPRODUCT in business rule or...
    Topic posted August 1, 2019 by Jash JoshiRed Ribbon: 250+ Points, tagged Financial Planning, Help Center, PBCS, Planning, Projects, Public Sector, Reports, SmartView, Strategic Modeling, Tip, Workforce 
    75 Views, 6 Comments
    Title:
    Has anyone used @CalcMgrExcelSUMPRODUCT in business rule or member formula?
    Content:

    We have a requirement to calculate the sum product in the formula and it should be dynamic based on the month selected by user.

    Following is the syntax documented by Oracle:

    @CalcMgrExcelSUMPRODUCT

    Purpose:

    Returns the sum of the products of corresponding array components

    Syntax:

    Java Class: com.hyperion.calcmgr.common.excel.cdf.ExcelMathFunctions.SUMPRODUCT(double[],double[])

    CDF Spec: @CalcMgrExcelSUMPRODUCT(values1, values2)

    I have applied the same in the member formula as below:

    @CalcMgrExcelSUMPRODUCT(@XRANGE("AKPI_DayMonth"->"JAN","AKPI_DayMonth"->"DEC"),@XRANGE("AKPI_TechAvailPctPower"->"Jan","AKPI_TechAvailPctPower"->"Dec"));

    Currently I am trying to get output using the static formula but this is not giving any result in the system.

    Any help on this will be great.

    Thank You.

    Regards,

    Jash Joshi

    Comment

     

    • Mark Rinaldi

      In thinking mode... i.e. asking smarter people than me for help!

    • David Ambler

      Hi

       

      Yes have used, modifying it a bit to your logic would read...

      @CalcMgrExcelSUMPRODUCT(@LIST(@DESCENDANTS(YearTotal)->AKPI_DayMonth"), @LIST(@DESCENDANTS(YearTotal)->AKPI_TechAvailPctPower))

       

      Cheers

      • Peter Nitschke

        Wait a minute here (and hey Dave!)

        Does that let us calculate weighted averages and driver costs dynamically? Interesting....could be an interesting outcome for a fully hybrid essbase cube...

         

        • David Ambler

          Hi Pete

           

          I haven't used it in that manner, but you should be able to... as an example I use it to calculate the income for months based on a generic profile, so if you look at the example Jan->Income is the result of the sumproduct of (Mth1:Mth6)->CC_NA->Base->Profile and (Mth1:Mth6)->Transactions;

          ie for Jan, 140 = 3*20 + 2*40, Feb, 200 = 1*10+3*30+2*50

            CC_NA
          CC1

          CC1

          CC1

          CC1

          CC1

          CC1
            Profile Transactions Transactions Transactions Income Income Income
            2019/2020 2019/2020 2019/2020 2019/2020 2019/2020 2019/2020 2019/2020
            Base Jan Feb Mar Jan Feb Mar
          Total         140 200 260
           Mth1 10   1        
           Mth2 20 3   1      
           Mth3 30   3        
           Mth4 40 2   3      
           Mth5 50   2        
           Mth6 60     2      

          BTW well done on Best Speaker at KScope

          • Peter Nitschke

            Thanks Dave!

            Yeah - that's interesting. It feels like you could create a truly dynamic driver tree recalculating the weighted average (not flat) as it rolled up 

            Would work in a Hybrid cube, without needing to tweak the solve order for the other dimensions to consolidate later.

            Something to have a play with sometime. 

            cheers

            Pete

             

          • HL1

            Hey Brains Trust :), I am trying to get the correct number in the red highlighted cell below (132). In Excel l get this with =SUMPRODUCT(D4:D11,G4:G11) but I can't find a way to get the second dimension (Employee Type) into the formula below, any suggestions please?

            "FTE x Rate" = @CalcMgrExcelSUMPRODUCT(@LIST(@DESCENDANTS("All Jobs")->"Employee Type"->"FTE"),@LIST(@DESCENDANTS("All Jobs")->"Employee Type"->"Base_Rate"->"BegBalance"));

                Assumptions Assumptions Assumptions
            Jul

            Jul
                Pay Frequency Base Rate ($) Shift Loading (%) FTE x Rate FTE
            Manager - Day Permanent Weekly 53 #Missing #Missing 2
            Supervisor - Day Permanent Fortnightly 26 #Missing #Missing 1
            Operator - Day Permanent Weekly 26 #Missing #Missing #Missing
            Operator - Day Casual Weekly 36 #Missing #Missing #Missing
            Maintenance - Day Permanent Monthly 40 #Missing #Missing #Missing
            Apprentice - Day Permanent Weekly 32 #Missing #Missing #Missing
            Operator - Afternoon Permanent Weekly 53 15% #Missing #Missing
            Supervisor - Night Permanent Fortnightly 26 30% #Missing #Missing
            All Jobs Employee Type #Missing 292 #Missing #Missing 3
            No Job No Employee Type #Missing #Missing #Missing   132 #Missing