8 Replies Latest reply on Jan 3, 2013 9:16 AM by Ian Devonald

    Computing a "Repetition" rate

    Dan Gerena

      What I am trying to do is to compute the rate at which employees have multiple "offenses" in a time period. So assuming all employees desired result is a single record (1), I want to know the rate at which the population exceeds the desired record count of 1 for a given period.

       

      So if I have the 5 employees below, employees 12345 and 33221 have more than 1 record, while the other 3 only have a single record. Thus the math I want to perform is 2/5 = 40%.

       

      I only want to present a single row showing "40%"...I do not want to show a record for each employee (i.e. 5 records). Is this doable? I can see how to do the math of which employees have more than 1 record at the employee level, but when I remove the employee field from the row shelf, the math no longer works...

       

      Employee #          Transaction Date  

      12345                    1/1/2012

      12345                    1/15/2012

      12345                    1/17/2012

      33221                    1/15/2012

      33221                    1/20/2012

      44444                    1/7/2010

      67777                     1/15/2012

      99999                     1/18/2012

        • 1. Re: Computing a "Repetition" rate
          Richard Leeke

          You can use table calculations and then a table calc filter to hide all but one row, as attached.

          • 2. Re: Computing a "Repetition" rate
            Dan Gerena

            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?

            • 3. Re: Computing a "Repetition" rate
              Richard Leeke

              Hi Dan

               

              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.

              • 4. Re: Computing a "Repetition" rate
                Jonathan Drummey

                Here's the link to a list of table calc resources that I think Richard is referring to:

                 

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

                 

                Like many things worth doing, practice is key.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Computing a "Repetition" rate
                  Dan Gerena

                  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.

                  • 6. Re: Computing a "Repetition" rate
                    Jonathan Drummey

                    How many copies should I put you down for?

                    • 7. Re: Computing a "Repetition" rate
                      Ian Devonald

                      Hi Richard

                      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

                      http://community.tableau.com/message/163567

                       

                      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"   

                      • 8. Re: Computing a "Repetition" rate
                        Ian Devonald

                        Sorry - just realised this thread is also called http://community.tableau.com/thread/108290.  Which is already referenced on Jonathans site.