Richard, thanks for your help...After looking at your solution,
WINDOW_SUM(IIF(ATTR([Employee #])==IFNULL(LOOKUP(ATTR([Employee #]), -1),0) AND ATTR([Employee #])!=IFNULL(LOOKUP(ATTR([Employee #]), -2),0),1,0))
...though it works, and conceptually, i get what the formula is doing, it seems to generate more questions along the lines of:
How does an average bear like myself learn to author formulas that use the ATTR function, and what the heck does "==" signify, etc.?
What resource do you use to become proficient in authoring such complex formulas? I presume many folks like myself would like to learn such techniques, but I'm not familiar with a resource to learn such advanced techniques...can you shed insight?
Good question. A few comments on the "how do I learn this stuff" and then I'll answer your specifics.
In my case, I've learned from a combination of sources: I've actually sat and watched the Tableau training videos on this stuff and read the pages in the help - but mainly it's been by a combination of trial and error and spending too much time hanging around on the forums. I've found that reading threads and studying other people's answers and also answering questions is the most effective way to learn. Back when table calculations first came out in Tableau 6 I posted a lot of questions - there was one monster thread in particular in which Ross Bunker (the Tableau brain behind table calculations) explained lots of stuff - and I still refer back to that. I've also picked up a lot from Joe and more recently Jonathan on the forums.
I have a feeling Jonathan may have posted a handy list of good resources for table calculations a while back - maybe he'll see this and post a link to it (if I'm not imagining it).
Now to your specifics.
ATTR() is a hard one to get your head around. I'll have a go at explaining when to use it.
Within table calculation functions such as LOOKUP() and WINDOW_XXX() you need to reference aggregate functions rather than just the raw field value. But very often you actually really want individual values - which means that you need to have appropriate dimensions in play in order for Tableau to have retrieved the individual rows. If you have succeeded in getting the individual rows, it really doesn't matter whether you take MAX(), MIN() or AVG() - they all come to the same thing. But if you just use AVG(), say, and you hadn't actually got all of the dimensions you needed, you might not notice that you were actually taking the average of several rows. This is where ATTR() comes in. It's a special aggregate function which checks whether there is more than one distinct value for the field that you are aggregating. If there is only one value, it returns that, but if there are multiple values, it returns a '*' - which immediately highlights that you haven't got something how you wanted it. So any time Tableau is insisting on an aggregate but you know you should just have a single value, use ATTR().
'==' just means 'are these two things equal?'. In Tableau you can also just use '=' to mean that. It's just that some programming languages use '=' to indicate that you are assigning a value to a variable and '==' to test for equality - and getting the wrong one can give disastrous results - so it's a fairly ingrained habit for me to use '==' for the equality test.
1 of 1 people found this helpful
Here's the link to a list of table calc resources that I think Richard is referring to:
Like many things worth doing, practice is key.
Awesome. Thanks for taking the time to explain.
The table calcs really represent a large, under-leveraged set of functionality in Tableau. In both Excel and SQL, I've used books to teach myself pretty much every formula, so I can do pretty much anything. The resources around Tableau table calcs are much more limited and scattered.
Jonathan, Richard, Joe, whomever: write a book using transactional data, and go thru examples (i.e. I want to count the instances of "x", etc.) and I'd be first in line to buy.
How many copies should I put you down for?
I've just been looking back through the archives for the thread that you mentioned. Apart from making my head spin and getting a little depressed at how much I don't understand. I think (for other users looking at this thread) this maybe the one you were referring to :
Version 6 tables calcs - understanding a few basics
Keep up the good work.
All the best - Ian
ps. Jonathan - Put me down for one of those books. Suggested Title "Breaking Rocks : Life on the Tableau Forum Chain Gang"