1 2 Previous Next 18 Replies Latest reply on Sep 26, 2014 12:16 PM by Matt Lutton Go to original post
• ###### 15. Re: Help on summing distinct values

Hi Matthew,

Thanks again for taking time to work on my problem. What I still don't understand is that for these two statements:

(1) IF FIRST()==0 THEN WINDOW_SUM(attr([Budget sales])) END

(2) IF FIRST()==0 THEN WINDOW_SUM(SUM([Sales])) END

They both have the same setting, e.g. compute using set to Category. So what makes (1) able to sum ONLY distinct budget sales whereas (2) sums EVERY sales? The IF First()==0 statement mainly is to control what is returned (i.e. only one value is returned). But what is this one value that is returned? - this is the main question (which is also my original problem), i.e. how to sum only the distinct budget sales. Is it because of attr() used in budget sales?

Also, the Addressing and Partitioning concepts are hard to grasp (for me). I've read the online tableau documentation plus some online articles, but they're still kind of confusing to me. It's the same thing as a college professor in a top college who is very well-known for his research, but also lousy in teaching (students can't understand what he's trying to say). I'll have to take some time to actually test out different scenarios on tableau desktop for (1) and (2), rather than depending on the online documentation or articles (not much help).

• ###### 16. Re: Help on summing distinct values

Von:  No problem at all.  I understand you having questions -- this stuff is complicated.  Part of the reason I don't have great, detailed answers is that I'm still learning all of the complexities myself.

But to answer your basic question, YES, it is the ATTR() that makes the difference here.  The ATTR() says take the distinct Budget Sales values, and aggregate them up (at the level of detail set by our dimensions and our "Compute Using" settings on the Table Calc).  WINDOW_SUM(SUM(Sales)) says take the aggregate sum of sales and further aggregate it to the level of detail we've specified.  I'm not sure if that is helpful, but a lot of this has come through experimentation until I get the correct results.  But there certainly is logic to it; I would welcome anyone to offer their thoughts/descriptions on the differences here as well.

The best illustrations/descriptions I've seen for addressing/partitioning are in the preface to Jonathan's blog post on "At the Level": At the Level – Unlocking the Mystery Part 1: Ordinal Calcs | Drawing with Numbers , but I must admit, that may be because I've been working on figuring these things out for several months.

In this example, with a Compute Using on "Category", the default "Partitioning" being on Region as a result.  To make that visual, it means that our "Partitions" are for each region, and the "Addressing Rows" for that partition are on Category.  That may or may not help, but this is one description I've begun to use when thinking about what I'm doing with Table Calcs.

I can tell you it takes time to grasp some of these concepts, and I'm not fully there.  I always suggest going through Jonathan's blogs, as there is a variety of helpful information there, as well as illustrative examples in various scenarios--I'll put this link here again for posterity, as it links to several Forum threads and other resources that help in understanding these concepts: http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/

• ###### 17. Re: Help on summing distinct values

Von T

Try this:  Take your initial values, with Region on Columns and Category on the Detail shelf (same setup as in the views I created), and then simply do a SUM(Sales) and an ATTR(Budget Sales)--I think that will help you see the difference, in terms of WHAT is being aggregated, and how they differ:

ATTR(Budget Sales) -- So, when we aggregate these up to the level we want, we get \$70,000 for West:

Sum(Sales) -- So, when we aggregate these up to the level we want, we get \$75,000 for West:

I think this will help you see what I've done with the Table Calcs a bit more clearly.  It is often helpful to look at your data in various ways to try and understand the various results Tableau may generate for you.  That's what I've tried to illustrate above.

• ###### 18. Re: Help on summing distinct values

And here is the workbook with those sheets added, showing the difference between the two calcs with Category and Region as the  dimensions setting our level of detail.

1 2 Previous Next