0 Replies Latest reply on Nov 1, 2018 7:16 AM by Adam Payne

    Formula based on calculated field in a hierarchy

    Adam Payne

      Hi everyone,

       

      I'm having trouble figuring out how to, and if it's even possible, to get my formula to work correctly  based on a calculated field that is in a hierarchy.

       

      I have a hierarchy that has 3 levels:

      -region

      -country

      -budgeted

       

      Each level of the hierarchy has a separate fundraising budget, but everything is rolled-up from the lowest level. For example, in our Southeast asia region, we have offices in:

       

      -Thailand

      -Philippines

      -Cambodia

       

      Cambodia only has one office, so the budgeted level and country level are on in the same. For Philippines, we have offices and separate budgets in Cebu and Manila. There is also a national budget that is separate from the offices and a separate part of the country overall budget. So for the Philippines the budget hierarchy is:

       

      Manila+Cebu+National=Philippines

       

      So far so good? Here's the problem: the national budget can be used to cover the office budgets if they are underfundraised but not vice versa. So if Manila and Cebu are over funded by a total of $1m and the national budget is under by $1m, then the Philippines as a whole is considered underfunded by $1m. However, if the national budget is over funded by $1m and Cebu and Manila are underfunded by $1m total, then the Philippines as a whole would be considered fully funded.

       

      To the regional level, countries with under-funded totals (that's offices+national budgets as described above) add to the regional fundraising deficit. As with offices, over funded countries cannot have their overages put towards other countries within the region.

       

      So that's all to get to my problem, I have the individual office/national budgets set and the country budgets set as well. However, since the 3 hierarchy levels are kinda overlapping due to country budgets being part of two levels, I have 2 different formulas for the two lowest levels of the hierarchy. For the regional level, it needs to be calculated based on the result at the country level, which is in turn calculated as a result of the budgeted level. I can't just sum them because that would be an aggregation of an aggregation and the value of the country-level total changes dynamically depending on which levels of the hierarchy are displayed.

       

      This is a very long winded explanation of my situation so I hope it's clear. I know everyone likes seeing packaged workbooks but my datasource is SalesForce and my extracts are too large to upload to the forums (this one is 130mb), google drive link in case you are willing to take a look Restricted Giving Report v5.twbx - Google Drive

       

      Office Budgets.PNG

       

      Country Budgets.PNG

       

       

      Region Budgets.PNG

       

      For this regional view, Southeast Asia should be calculated from the country level as $701,397 - $106,630=$594,767 (Null at the country level is money given directly to the region and can be used to cover any underfundraised amounts)

      and Africa region should be $2,166,693

       

       

      It may be that without being able to see the workbook it's not possible to help with a definitive answer but any general guidance on calculating values within hierarchies would be appreciated!!!

       

      In case someone is willing to download a big file: Restricted Giving Report v5.twbx - Google Drive

       

       

      Here are my relevant formulas:

       

      Budget remaining for the year:

      { FIXED [GAU]: sum([FY Budget])}- { FIXED [GAU]:max([Weighted Allocations+Grants+Rollover])}

       

      Fundraising remaining for the year at the lowest level:

      IF [Budget Remaining]>0 Then [Budget Remaining]

      Else 0

      END

       

      Fundraising remaining for the year at the country level:

      IF  sum({ FIXED [GAU]:max(IF [Fundraising Remaining GAU Level]>0

      THEN [Fundraising Remaining GAU Level]

      ELSEIF [GAU]='Philippines' OR

      [GAU]='Thailand' OR

      [GAU]='South Asia' OR

      [GAU]='Uganda' Then

      [Budget Remaining]

      ELSE 0

      END)})>0

      THEN

      sum({ FIXED [GAU]:max(IF [Fundraising Remaining GAU Level]>0

      THEN [Fundraising Remaining GAU Level]

      ELSEIF [GAU]='Philippines' OR

      [GAU]='Thailand' OR

      [GAU]='South Asia' OR

      [GAU]='Uganda' Then

      [Budget Remaining]

      ELSE 0

      END)})

      ELSE 0

      END