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

Multiple Nested Table Calculations

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.

• 1. Re: Multiple Nested Table Calculations

Oh my goodness, I just figured it out. I guess looking at the problem from the outside by posting this helped .

I can find the denominator grand total by doing: [TC Quantity by Date Range] / WINDOW_MAX(RUNNING_SUM([TC Quantity by Date Range]))

Any other/better thoughts welcome!

Best,

Jennifer

• 2. Re: Multiple Nested Table Calculations

Glad you got it Jennifer...I was going to suggest a WINDOW_FUNCTION of some type to help you along. They seem to play well with nested table calcs.  Don

• 3. Re: Multiple Nested Table Calculations

Thanks Don, and good to know that WINDOW functions are more forgiving in general.

I avoid Table Calcs whenever possible as multiple people handle the workbooks I create and Table Calcs are so easily invalidated by any change to the view.  But sometimes they're the right or only way to go!

Best,

Jennifer