9 Replies Latest reply on Nov 27, 2012 12:27 PM by Jonathan Drummey

    History of Ranking (Pulling ranks from different worksheets?)

    Chris Tsui

      You know, there are some days where I'd like to feel self sufficient and be able to go it alone without having to come to the community for help...

      today is not that day


      Have a question for you all!


      I've been following Richard and Johnathan's different guides on dealing with this issue of Quantiles and ranking.  For the most part i've been able to follow along and have been able to get results of the bottom 33% (third) for different measures.  Now what I'm hoping to do is to look for bottom performers across time and measure criteria


      (Using Superstore data)


      What I did was create three worksheets that would rank Customer's order's based on three measures (QTY, Profit, Unit Price) 

      I then filtered each worksheet to only show the bottom 33% of orders

      I placed these three worksheets on a dashboard

      Created a "dummy summary sheet" and placed it in the dashboard as well.


      This is where I got stuck


      What I'm hoping to do, is to create a calculation that goes through the results of each worksheet and find if there are order numbers that appear in the bottom 3rd of at least two measures over two consecutive quaters and then display it in my summary sheet.


      Granted the super store data and this question aren't very logical, primarily I'm looking to figure out some sort of performance dashboard where my product providers score in the bottom third consecutively over time and in x number of measures to have it show up in my summary so I can address it.  Is this even possible?


      In my head I envision a function/calculation that counts (in the super store example) the if the order number appears in 2009 Q1 in each worksheet and some sort of case statement where if the count is = 2 then show in Sheet 4?  Where I get stuck is how to iterate across worksheets (Or if I even can)


      I've included a superstore TWBX as an example.  Would appreciate any help the community can lend!  Or even a better way to do this  




        • 1. Re: History of Ranking (Pulling ranks from different worksheets?)
          Chris Tsui

          Hi All,


          Was kicking this around in my head last night and had two thoughts:

          1. Is there a way to create something like a user defined variable, array etc. in Tableau?  Something where I could populate data which I can then query from a seperate worksheet?  I imagine then I could just run a boolean calculation to see if order is in bot 33% in Year and Year - 1.  I could then keep track of all those that return true


          2. Would Action Filters work?  Somehow when clicking the individual worksheets it would throw the results of a filter for the bottom 33% into a fourth worksheet?


          Not sure if this even makes sense, but was kicking it around last night and thought I'd throw it out there?

          • 2. Re: History of Ranking (Pulling ranks from different worksheets?)
            Chris Tsui

            Played with actions over the weekend to no avail.  It looks like all it will do is pass filter criteria vs. a data set to another worksheet?

            • 3. Re: History of Ranking (Pulling ranks from different worksheets?)
              Chris Tsui

              Would love to see if there's any input to this one

              • 4. Re: History of Ranking (Pulling ranks from different worksheets?)
                Shawn Wallwork

                Chris, you've been working so hard at getting some attention on this issue, and you've done the right thing by posting a packaged workbook. I thought I'd ping a couple of guys for you to see if that helps. Certainly Richard and Jonathan might be interested since you started with their work. Also Dimitri is around today (I know because he just gave me a piece of advice on another thread) and he might have interest in this sort of question. Also, Tracy might take a swing at this tomorrow.


                Hope this helps.



                1 of 1 people found this helpful
                • 5. Re: History of Ranking (Pulling ranks from different worksheets?)
                  Richard Leeke

                  Silence often means that nobody can think of a way of doing it but nobody is quite brave enough to say it can't be done.


                  But now that Shawn has made me take a closer look , I'll throw a couple of comments out there.


                  First off, I can't think of any way to pass the results from multiple sheets to another sheet.


                  But maybe you don't need to. If all the measures you want to rank on are from the same data connection, you can probably do all of your ranking calculations in a single worksheet.


                  No time to try to put an example together right now - but you might be able to do calculated fields that evaluate your individual criteria and then a final one which combines those. Probably quite tricky table calcs, but might just be possible.


                  So if you defined [Bad_QTY] to return 1 if the customer ranks in the bottom 33% 2 months running and 0 otherwise. Ditto for [Bad_Profit] and [Bad_Unit_Price].


                  Then add those 3 fields together and see if the answer is >=2.

                  1 of 1 people found this helpful
                  • 6. Re: History of Ranking (Pulling ranks from different worksheets?)

                    I would suggest the same approach - instead of thinking of separate sheets, think of custom calculations and table calculations. Some of it has already been done - there are table calcs to figure out quantiles and bottom 1/3rds.


                    As for the two consecutive periods test - I think an IF statement using PREVIOUS_VALUE might be of use, i.e., using Richard's suggestion, IF previous value in bottom 1/3 and current value in bottom 1/3 then 1, else 0.


                    But it does look like a lot of work with some advanced table calculation acrobatics.

                    1 of 1 people found this helpful
                    • 7. Re: History of Ranking (Pulling ranks from different worksheets?)
                      Jonathan Drummey

                      Like Richard and Dimitri said, this is doable, but requires some table calc acrobatics. A couple of notes:


                      - The percentile calc uses SUM(Number of Records), however in Superstore Sales there are 1 to 6 records per Order ID (there's a Row ID that's unique and hidden in the Measures section), so the percentile calcs will be incorrect. Also, in the Superstore Data, Order Numbers have a unique Order Date so they won't repeat over time and the calcs won't work out as you wish to do comparisons over time. 


                      - Let's use Customer instead of Order ID for this example: You stated you are looking to find customers who are in the bottom 33 percent across two quarters for at least two measures. The Superstore Sales data does not include rows for customers who didn't buy anything, and in any case is somewhat sparse (I did some work awhile back on ranking repeat buyers in the Superstore Sales data and ran into this). Where this causes an issue is, what is the set of customers you are really interested in? If you start with the set of customers on 1Q2009, then what do you do about the ones who didn't buy anything in 2Q2009? Conversely, if you start with the set of customers on 2Q2009, then what to do with those customers who didn't buy anything in 1Q2009? You may need to pad the data, either by domain padding in Tableau or in the SQL, and in either case would need to alter the percentile calcs to appropriately deal with the extra rows.


                      I'd be happy to help with this, but would need answers to those issues.



                      1 of 1 people found this helpful
                      • 8. Re: History of Ranking (Pulling ranks from different worksheets?)
                        Chris Tsui

                        (I'm Canadian so I'm using this example!)

                        Do you remember as a child, when you would watch Hockey on TV, then play outside on the street pretending to be that superstar?  Then attending your first game and getting to visit the locker room where you see all the stars in real life?  That's kind of what this thread is like


                        Then you watch them play and realize they're SOOOO much better at it ......


                        Am appreciative Shawn of the gathering of minds and the responses that its illicited!

                        Also am now seeing the Hill of "Table calc acrobatics" ahead of me haha


                        I never thought about combining all of it together into one worksheet (maybe out of fear) but I'm going to give that a try.  Thank you for the suggestion.


                        Johnathan, to answer your question (and If I understand it correctly and using the customer context)  I would probably want to treat those that didn't have any "activity" in the quarter as nulls and filter them out?  not sure if that makes sense. 


                        If they purchased in Q1 and not in Q2, I would exclude them from the percentile calcuation in Q2.  If they again made a purchase in Q3 I would want to include them once again.  In thinking this part through would some sort of blanket check of "if no activity then don't include in calculation" work? Maybe something like "Sum number of records minus count of customers where sales = null?"


                        Going to give combining it all a try as well....


                        Once again, am very appreciative of all the suggestions and thoughts!

                        • 9. Re: History of Ranking (Pulling ranks from different worksheets?)
                          Jonathan Drummey

                          In the view you posted (everything on Rows), by default Tableau won't do any domain completion or padding, so that will line up with what you describe. Let us know how it turns out, and if you have any more questions!