8 Replies Latest reply on Feb 17, 2016 1:20 PM by Daniel Vincent

# Sum of Grand Totals Percentages/Products

Hello,

I'm hoping someone can help me on this one.  How do I break down each of the product types into a grand total ( essentially quarter to date ) for "quantity" and "subscription %"? So at the end I would like to see  "Quarter To Date" columns for both "Quantity" and "Subscription %" to include "Production","Wild","Threat","URL", and "Global P" for each column.   The dates/data on this workbook will change, as I need to report on each week in the quarter.

Also, it looks like since some of rows are blank, it needs to populate the NULLs ( blanks ) as a "0", so the sum and % calculations can work. I don't believe NULL will calculate, correct me if I'm wrong.

P.S. Pooja G - you had helped me with this original template....thanks for all your help!!!

• ###### 1. Re: Sum of Grand Totals Percentages/Products

Hi Eric,

I haven't gotten to the grand total situation yet, but I can help you out with the zeroes.

First, create a calculation using IFNULL that looks at your Quantity field, and returns zero as a condition if the sum of quantity is null.

Then you need to edit your Test Calc to include an IFNULL function as well.  I've attached a workbook (Tableau 9.2) that has the calculations on a separate worksheet called Anns Work.

That should help with part of your situation. (I need to tinker a little more to see if I can help with the other part)

-Ann-

• ###### 2. Re: Sum of Grand Totals Percentages/Products

Actually, to clarify - what are you trying to get to with the Grand Totals?  You already have them in the visualization at the row and column level (all the way at the bottom), and they look correct - especially after I added in the zero calculations to account for the nulls.   Did that solve your issue?

-Ann-

• ###### 3. Re: Sum of Grand Totals Percentages/Products

You can get zero with one calc:  ZN(LOOKUP(SUM([Quantity]),0))

As for GT's, what exactly do you need to see?  As Ann stated above you have them for the most part.  What is your expected result needed?  What constitutes Subscription %?

• ###### 4. Re: Sum of Grand Totals Percentages/Products

I need to see the Grand Totals for "Production", "Wild", "Threat", "URL", and "Global P" separately, not the grand total for all of them combined.

The "% Subscription" is each product:  "Wild", "Threat", "URL", and "Global P" divided by "Production". For example "Wild" divided by "Production", "Threat" divided by "Prodution", "URL" divided by "Prodution", and "Global P" divided by "Prodution" in it's own column.

In the end, I would like to see two Grand Totals: 1 for the Quantity of "Production", "Wild", "Threat", "URL", and "Global P". And 1 for the % Subscription for "Wild" divided by "Production", "Threat" divided by "Prodution", "URL" divided by "Prodution", and "Global P" divided by "Prodution" in it's own separate columns.

• ###### 5. Re: Sum of Grand Totals Percentages/Products

Why would you divide by Production, when that's one of the measures (and would come up with a percentage of over 100% when divided by URL which seems to trend larger than Production)?  Did you mean to say that you want to divide by the sum total of all of the measures, or is there something special about Production?

• ###### 6. Re: Sum of Grand Totals Percentages/Products

Production is a special value. It's hard to explain, but yes the % could be above 100%

• ###### 7. Re: Sum of Grand Totals Percentages/Products

I'm afraid you might have me stumped on the final mile.  Hopefully someone else can come through.  I suspect you can use a window calculation, but nothing I've worked through works yet.

• ###### 8. Re: Sum of Grand Totals Percentages/Products

Is this what you want?