3 Replies Latest reply on Nov 24, 2018 1:49 PM by Jennifer VonHagel

    Multiple Nested Table Calculations

    Jennifer VonHagel

      Hi all,

       

      I am trying to find % of Total on a calculation that already has multiple nested table calculations. Can this be done?  Here's my set up (packaged workbook is attached).

       

      On the first tab, you'll see the basic calculations I've set up so far.  All my calcs and parameters are in folders starting with "aaa".

       

      1. I find the Quarter of Order date, as everything is based on Quarters.

      2. For the purpose of this question, quarters will always be trended and I always need to see the most recent 8 quarters - not a problem, I've figured that out.

      3. I have a parameter that allows the choice to see Quantity by Quarter, by rolling 4 quarters, or by YTD as of the Quarter selected.

      4. I have a second parameter where the user can select a quarter.  Whatever quarter they select, they will see this quarter and 7 quarters previous.

       

      5. I have a calculation called TC Quantity by Date Range. This finds SUM(Quantity) based on the user's selection:

       

      6. TC YTD Quantity is its own Table Calculation, as it needs be addressed and partitioned differently than the other Table Calcs:

       

      If you click around on the first tab in the workbook: Basic Table and Calcs, you'll get the gist of what this is meant to do.  I think it's all working fine.

       

      MY QUESTION

      I want to find the % of Total of [TC Quantity by Date Range] across SubCategory (still trended).

      1. In the Edit Table Calculation, there is no option for a secondary calculation. 

      2. If I try to do [TC Quantity by Date Range] / TOTAL([TC Quantity by Date Range]), I get an error that TOTAL() can't have a table calculation as an input.

       

      What can I do?

       

      Many many thanks!

      Jennifer

       

      P.S. If it helps I could live without the YTD option, though I of course prefer to keep it.