5 Replies Latest reply on Aug 23, 2018 9:06 AM by Danny McKenna

# Table Subtotal with ZN Padding

I have a bit of an obscure question for tableau, so any help is appreciated! I have a table that lists values from groups over a time span, with the groups on the rows, and year/quarter/month on the columns, and the value in the table is summed. I have the sub totals set to average, so we can compare monthly expenses, along with quarterly averages (as the value can change quite a bit month to month, we've found it handy to have both monthly sums, and quarterly averages)

The issues start to pop up when there’s a blank/null value in the table, the quarterly averages don’t consider that column as a value that it should use in the average denominator (I’ve attached a work book, check out the normal tab, Q1 2018, group B or D to get an example of how the quarterly average is an average of the 2 values, and not 3 months). So to fix this, I replaced sum(value) with zn(lookup(sum(value),0)), to replace the table null values with a 0 (see the with padding of zero’s tab in the attached example). This seems to work on everything except the quarterly averages, which now are defaulted to sums, and can’t be changed.

If someone has a tip/trick/way to get the average subtotal column to either use all the values (even if null) or how to change the subtotal column to average when using the zn function, I would be profoundly appreciative!

Thanks!

• ###### 1. Re: Table Subtotal with ZN Padding

Could you please find the attached workbook and see if it's the solution you are lookingfor

Thanks

1 of 1 people found this helpful
• ###### 2. Re: Table Subtotal with ZN Padding

alternate solution to pad with 0 when  there is no data.

check sheet validate1,

the calculation isQuarterSubTot  returns true for the total cells.

first() = last()    computing Year,Quarter,Month  restarting  Quarter

the index() function on the detail shelf allows to have a label on every mark.

You can see on sheet Validate2 , if you remove it from the Text shelf you lost the True on the  Group E  Q4 total.

On final view sheet,  with index still on the detail, you can compute the values with

if [isQuarterSubTot] then ifnull(SUM( [Value] ) /3,0)

else zn(lookup(SUM([Value]),0))

end

Michel

1 of 1 people found this helpful
• ###### 3. Re: Table Subtotal with ZN Padding

Thanks for the reply MADHURI.TANNIR! This works, until I put a filter on the date (see attached), if the filter excludes a month, in the example, Oct 2017, the Get the Avg calc is still dividing by 3, which throws off the sub total column. I tried to use size() to help this, but it seems like the size() is doesn't change from each partition per subtotal (stays 2 for Q1 of 2018)

Again, all help is greatly appreciated!

• ###### 4. Re: Table Subtotal with ZN Padding

Danny,

You can go with

if [isQuarterSubTot] then ifnull(SUM( [Value] ) /[Nb month in view],0)

else zn(lookup(SUM([Value]),0))

end

Where  Nb month in view is

WINDOW_MAX( [Nb month in view per group] )

and  Nb month in view per group is

WINDOW_SUM(COUNTD(DATEPART('month', [Date])))

pay attention to the computing of all calculations in the final  nested calculation

Michel

1 of 1 people found this helpful
• ###### 5. Re: Table Subtotal with ZN Padding

Thanks for the info! That is some impressive nested table calcs there, I have been trying to replicated the results on a new tab in the same work book that you attached, and can't quite get it to match yours. I will tinker around with the computing options for the final calc, and see if I can match your results.

Thanks again for the help!

Danny