12 Replies Latest reply on Dec 5, 2012 4:34 PM by Jonathan Drummey

# Summary Function at Individual Row Level

I have struggled with this kind of thing several times.  I have always got around it, but don't think I can this time.  Any ideas would be greatly appreciated.

I have a table where I list the average and standard Deviation for each Diagnosis.  I can then calculate the metric I need (mean + 1 stdev).  HOWEVER, what I need to do now, is take this calculation and then compare to the individual values.  If it is greater than this, I want to calculate the difference between the two and sum them up for the given Diagnosis.  For example,

 Diagnosis Case Cost Diag Stdev Diag Mean mean + 1 stdev if above include 301 1 \$3,492 \$772 \$2,885 \$3,657 \$0 301 2 \$2,393 \$772 \$2,885 \$3,657 \$0 301 3 \$3,049 \$772 \$2,885 \$3,657 \$0 301 4 \$2,039 \$772 \$2,885 \$3,657 \$0 301 5 \$4,033 \$772 \$2,885 \$3,657 \$376 301 6 \$2,093 \$772 \$2,885 \$3,657 \$0 301 7 \$2,720 \$772 \$2,885 \$3,657 \$0 301 8 \$3,029 \$772 \$2,885 \$3,657 \$0 301 9 \$4,000 \$772 \$2,885 \$3,657 \$343 301 10 \$2,002 \$772 \$2,885 \$3,657 \$0 301 Total \$772 \$2,885 \$3,657 \$718 302 11 \$9,807 \$29,199 \$21,757 \$50,956 \$0 302 12 \$80,796 \$29,199 \$21,757 \$50,956 \$29,840 302 13 \$7,079 \$29,199 \$21,757 \$50,956 \$0 302 14 \$8,079 \$29,199 \$21,757 \$50,956 \$0 302 15 \$80,769 \$29,199 \$21,757 \$50,956 \$29,813 302 16 \$8,405 \$29,199 \$21,757 \$50,956 \$0 302 17 \$8,965 \$29,199 \$21,757 \$50,956 \$0 302 18 \$9,304 \$29,199 \$21,757 \$50,956 \$0 302 19 \$9,304 \$29,199 \$21,757 \$50,956 \$0 302 20 \$9,820 \$29,199 \$21,757 \$50,956 \$0 302 21 \$7,000 \$29,199 \$21,757 \$50,956 \$0 302 Total \$29,199 \$21,757 \$50,956 \$59,653

Thoughts?

• ###### 1. Re: Summary Function at Individual Row Level

Here we go. I calculated means and stdevs in Tableau - I prefer to do that. The only caveat is that this makes it more difficult to create the totals. There may be a better way I can think of.

• ###### 2. Re: Summary Function at Individual Row Level

Trivial if you don't need the rest of the data - see second sheet

• ###### 3. Re: Summary Function at Individual Row Level

I looked at this one, and process-wise I can't think of a better way to do it. There are some performance optimizations that can happen, namely the Mean and StDev calcs could be wrapped in PREVIOUS_VALUE(), and those WINDOW_ calcs have the ,0,IIF(FIRST()==0,LAST() added after the variable declaration. The Total calc can probably get away without any performance optimizations.

• ###### 4. Re: Summary Function at Individual Row Level

This is EXACTLY what I am looking for.  Can I ask a couple followup questions?

First, how does it know to do the average over the Diagnosis and not the entire window.  You don't have a Start and an End clause.  The one thing that you did do differently from what I tried was you used ATTR inside the window_avg, rather than what I had done (I tried both sum and average).  Perhaps with the ATTR it knows the "group" on which to aggregate?

Second, the "trivial" of the second page was most illusive to me.  It seemed like whenever I used a window function, the other data had to be showing in order for it to calculate correctly.  Perhaps this works because you put the Case (case Id) in the Level of Detail.  This seems pretty good and I assume I could still do other aggregates as long as I also do a window function for them as well.

Admittedly, Windowing functions I need to get a better grasp of how it works.  This is fabulous though.  Thanks so much!

• ###### 5. Re: Summary Function at Individual Row Level

Hi Karl,

Here's an answer to your first question:

ATTR() is a special aggregation that says, "return the value of [variable] if there is one and only one value across the partition, otherwise return a special form of Null (which Tableau indicates as an asterisk *)". What Alex did by using that is to make sure that no other Costs were going to be accidentally included. It's a useful error-catching technique because if there were somehow more Cost rows included, the calc would return * and cause the WINDOW_AVG calc to fail, instead of an incorrect result.

The WINDOW_AVG calcs have their Compute Using set to Cost, which means they will address (calculate a value for every) value of Cost, and partition (restart calculations) for each new Diagnosis. This is one of the (many) confusing things about table calculations, we often think of the partitioning first but Tableau has us specify the Compute Using/addressing. Whatever dimensions are not assigned to addressing are part of partitioning.

Now for the second question:

I'm going to guess that other times that you've used Table (Down), Table (Across), etc. for your Compute Using. Those all use what Tableau developers calls "relative addresssing," in other words the table calculations are completely dependent on the arrangement of the pills in the view and if you move pills around, the table calculation results change. What Alex did was to explicitly set the Compute Using to Case, which is "fixed addressing," and when we do that then we can move pills around and the calculations won't change, except for some more advanced situations.

I put together a list of useful materials in learning table calculations, they are wonderfully powerful and take some time to master:

http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/

Hope this helps!

Jonathan

1 of 1 people found this helpful
• ###### 6. Re: Summary Function at Individual Row Level

Great write up - thanks Jonathan. I could get into this - I post a tbwx and a four word summary - you do the heavy lifting and actually explain what I did...

I had never thought of using previous_value as a way to reduce the calculation load when you need the result of the table calc available to all members - very nice.

• ###### 7. Re: Summary Function at Individual Row Level

You're welcome, and you can thank Richard Leeke for the PREVIOUS_VALUE() trick!

• ###### 8. Re: Summary Function at Individual Row Level

Thank you both!  Excellent information.  I really appreciate you taking the time!

• ###### 9. Re: Summary Function at Individual Row Level

OK, I hate to do it, but one more question.  Any way to sort the final aggregate number descending?  I want to show the big \$ up top.  I fear that since sorting happens before the aggregation, it can't be done.

• ###### 10. Re: Summary Function at Individual Row Level

You can do it by using Tableau's built-in automatic alphanumeric sort. Create a calc that is -[Total] and set it to Discrete, then put that as the left-most pill on Rows, and uncheck Show Header. Ctrl+Copy the pill onto the Filters Shelf to exclude Nulls. I set this up in the attached. It's non-intuitive and hacky, but gets the job done.

Jonathan

1 of 1 people found this helpful
• ###### 11. Re: Summary Function at Individual Row Level

THAT is brilliant.  Thank you so much!

• ###### 12. Re: Summary Function at Individual Row Level

You're welcome!