12 Replies Latest reply on Jan 18, 2015 3:41 PM by Chris McClellan

    Indexing, Partioning and Addressing

    Erik Applebaum

      Background: I work for a cruise line that has multiple ship board casinos operating in several different markets where some of the casinos outperform the others drastically.  As a result, I can not just compare one ship to another and need to index the values in order to determine what themes are working the best.  It is entirely possible for one ship to have the same theme installed in multiple machines.  I can get the median value for each theme type and for each ship.  But the problem I am encountering is that I can't seem plot the median value for that theme where the median is first determined relative to that themes ship.

       

      End Goal: Create a dashboard that displays the median indexed value for a particular game relative to its respected ship

       

      In the attached workbook I have a tab (Median Index by ship) that has the median value plotted for each theme on each ship, but the problem with this view is that it games that appear on multiple ships display a greater "total" value.  On another tab (Median Index combined) it shows the median value overall, in other words no longer segmented by ship.  And as mentioned above I can not do this because of the play differences between casinos.

       

      I believe the way to fix this is to address the ship in the table calculation without having the ship on the marks card?  However, I am not sure if this is possible.

       

      Any help would be greatly appreciated.  Not sure if what I said above is clearly described so please ask if not.

        • 1. Re: Indexing, Partioning and Addressing
          Vladislav Grigorov

          Hi Erik,

           

          Have a look at the attached example. I made use of a data blending technique demonstrated by Mark Reuter at TCC13, in order to allow for comparison of aggregates at different levels - e.g. for the theme in this ship vs. the average for the ship, or for the theme in this ship versus the overall average for this theme across all ships. I started with a clean data source - not to ruin what you did already, and then copied the datasource and renamed it and the single calculation I did to be able to distinguish. The trick is in activating/deactivating links between the data source copies for the blending to do what we want. Then calculating the indexes is as simple as regular aggregate calculations, and saves you the hassle of making table calcs work.

           

          Hope this gets you started!

           

          Vlad

          • 2. Re: Indexing, Partioning and Addressing
            Erik Applebaum

            Hi Vladislav,

             

            Sorry for the delayed response, my attention has been elsewhere and the holidays came.  Thank you for your response but I still encounter the same problem with the above solution which is not being able to combine a Theme and the average or median indexed value relative to the ship.

             

            In the QH Pro Totals tab, it shows that we have some of this theme on each ship.  The value I am trying to get to is the overall average indexed value after the index is computed relative to the ship level first.  On ABC the index is 1.57 where on XYZ the index is 1.69.  The value I want to get to is 1.63 or the average of the two after the index is calculated at the ship level.  However, tableau is going to combine the underlying data then compute the index which would give me an overall index of 1.61.  I know the difference is slight here as this is not the most ideal example but it can be much more profound depending on what ships are used in the comparison.

             

            Also, attached is an quick excel workbook showing the math if that helps point out what I am trying to get to.

             

            Any insight you could give would be very much appreciated.

             

            Erik

            • 3. Re: Indexing, Partioning and Addressing
              Matt Morgan

              Erik,

               

              I looked at what you want your end result to be and it is basically an average of an average. That generally is not a very accurate way to report results. If you really need to go this route, you might have to have this index result in Excel. One thing I have learned about Tableau is that performing calculations is not as easy as it is with Excel. This is definitely a weakness of the program. Tableau starts to get goofy once you have aggregate functions, it limits the calculations it allows you to do on top of that.

               

              If you can, I would suggest you set up your dashboard similar to what you have on your spreadsheet example. I have dashboards set up where I have overall casino performance, like what you have on line 7 & 8, then I have themes & promotions etc in a separate window so I can see how they perform against the overall performance of the casino floor. I can select a certain theme, or even a single machine and look at the its performance on the rest of the casino. My Marketing and Slot people love that.

               

              I know I didn't give the exact answer you want. I am not an expert on the Tableau, but I don't think you can do what you want to do. Of course I could be wrong. I have a Casino/Gaming page set up under the Groups section on the website. Please check it out. There aren't any casino experts at Tableau so they don't understand how we utilize data and aren't much help when you call them.

               

              Matt

              • 4. Re: Indexing, Partioning and Addressing
                Erik Applebaum

                Matt,

                 

                Because of the nature of the cruise business being a world wide operation, and with some markets literally being in upwards 300% better than another, I have little choice but to do an average of an average which is not very good practice at all.

                 

                I don't think what I am trying to accomplish is possible either but I will definitely check out your dashboards.  Hopefully, I'll find something that can carry over.

                 

                Thanks,

                Erik

                • 5. Re: Indexing, Partioning and Addressing
                  Vladislav Grigorov

                  Hi Erik,

                   

                  Have a look at the attached workbook. The 1.63 number is definitely achievable via a table calculation, with all concerns that Matt expressed. I hope this gets you started, and also highly recommend these two materials for further education:

                  1) Ratio of Ratios with Nested Table Calculations - a short video in which (the great) Joe Mako explains the intrinsics of achieving such convoluted calculations.

                  2) Master Tableau Approach - a blog post by Keith Helfrich - one of the best explanations I've ever seen on what is the best way to tackle a problem in Tableau, and why.

                   

                  Best,

                   

                  Vlad

                   

                  PS. I was too fast to post this without the attachment . By the way, pay attention that the addressing of Calculation 2 is set to Ship in the view in order to get to the 1.63 number.

                  • 6. Re: Indexing, Partioning and Addressing
                    Chris McClellan

                    Hey Erik, can you email me please ? (email visible in my profile).

                    • 7. Re: Indexing, Partioning and Addressing
                      Chris McClellan

                      I downloaded your latest workbook and created a Custom SQL to get the average as 1.619 .... I've checked the data and that's correct, so the data must have changed at some point from 1.63.

                       

                      Anyway, I'm a "data guy", and still pretty new to Tableau so my solution is really based on ... the database should do the crunching, and leave Tableau to display the data in the best way.

                       

                      Check Dashboard1 in the attachment and see what you think.  Please email for some "trivia" as well.

                      • 8. Re: Indexing, Partioning and Addressing
                        Erik Applebaum

                        Hi Chris,

                         

                         

                         

                        The problem with having the database do the data crunching is that the extract used to pull the data into Tableau grows and as a result performance will decrease.  Also, I do not want to have to alter the back end query every time a change needs to be made. 

                         

                         

                         

                        • 9. Re: Indexing, Partioning and Addressing
                          Vladislav Grigorov

                          Erik,

                           

                          if you are patient to wait until the end of Q1, and have active license maintenance to get your hands on version 9.0, I believe you will be able to achieve your analysis in a much easier way through the newly introduced Level of Detail (LoD) calculations. For the time being, I believe table calcs or custom SQL are the possible ways to go though.

                           

                          Vlad

                          • 10. Re: Indexing, Partioning and Addressing
                            Erik Applebaum

                            Vlad,

                             

                            Thank you for all your help, I am patient and do have an active license so hopefully the LoD will be exactly what I need.

                             

                            Erik

                            • 11. Re: Indexing, Partioning and Addressing
                              Chris McClellan

                              Hi Erik,

                              I doubt that the data would grow that much to affect the performance, the amount of data that you're dealing with is really quite small (there's only a low number of ships, and a low number of  machines/games on each ship) compared to what I'm used to dealing with and what Tableau can process.

                               

                              Also, if you get the back-end calculation right, then there is no need to do maintenance on it.

                               

                              Anyway, that's just my opinion.  As I said, I'm new to Tableau but I've already seen a lot of problems with data crunching that is a lot easier to do in the database because Tableau doesn't have the necessary functionality to process the data properly ... maybe the new v9 features will help though.

                              • 12. Re: Indexing, Partioning and Addressing
                                Chris McClellan

                                Erik Applebaum, can you email me please ? (my email should be visible in my profile).  Cheers