12 Replies Latest reply on Apr 14, 2013 10:24 PM by John Zhu

    my use case in Tableau

    John Zhu

      yesterday I posted a question but got no reply. perhaps I am not speaking clearly of my needs so I tried again today and really need some help here.

       

      i worked in game industry and here is my use case:

       

      1. we keep a daily aggregated counter in table like below.

      date       game_Id       #users

      04/05           1                1000

      04/06           1                 2000

      04/05           2                 1500

      ...

       

      2. we also keep some daily distribution tables from user profiles, taken browser as example (we have distribution tables for many dimensions like gender/age/geo and etc.)

      date    game_id       browser (name+version)      ratio (normalized per game)

      04/05           1                 IE_8                                                0.1

      04/05           1                 IE_9                                                0.15

      04/05           1                 Chrome_15                                    0. 5

      ...

       

      What I want to achieve here is analyzing the #users across date given one specific game_id and the selected browsers (along with other dimensions). I am hesitate to do any table join here since our data volume is pretty big (whatever filters I set up it seems the table join will produce all combinations). So what I did is just create separate data connections to our counter table and other distribution tables. For given game_id, I use a couple of quick filters for user to select the browsers in interest, I could quickly get a number X with the logic like "select sum(ratio) where game_id = 1 and date = '04/05' and browser in ('IE_8', 'IE_9') in one sheet. Then I want pass X to another sheet for plotting a curve (X * #users) over date in the counter table. I am stuck in the last step since I did not find any logic for doing this simple thing in tableau.

       

      Any help and advices really appreciated ...

       

      John

        • 1. Re: my use case in Tableau
          Shawn Wallwork

          John post a sample workbook with the data connections set up the way you described, and with a couple of hundred rows of data for each connections. Set everything up the way you describe, then we can help you with that last step.

          • 2. Re: my use case in Tableau
            John Zhu

            shawnwall, I attached the workbook with greatly simplified data.

             

            a bit explain on my request: i created two sheets of Counter and Browser which is based on two data source (I do not want to produce joined view since our data volume is huge for joining everything together). Basically, I  want to pass the result in "Browser" sheet to "Counter" sheet. Currently, I added a parameter "Browser Ratio" which can be set manually from the "Browser" result but that is really not the ideal way we want to do.

             

            Looking forward to your help :-)

            • 3. Re: my use case in Tableau
              John Zhu

              Shawn, do you have time to take a look at my case? I really need someone to help me out...

              • 4. Re: my use case in Tableau
                Shawn Wallwork

                Sorry dude I forgot about this, thanks for the reminder. I'll have something to you first thing in the morning (MDT USA).

                 

                --Shawn

                • 5. Re: my use case in Tableau
                  Shawn Wallwork

                  John now that I see what you're up to, I think all you need to do is adjust your thinking on how Tableau works. It doesn't work with 'variables' like you're talking about. So you can't store a result as a variable and then use it. But in your case I don't think you really need to. If you make those two filters global, then when you create the new worksheet the SUM(ratio) will be the same and you can create a calculated field to produce your chart. (I would have taken a whack at doing this, but you did provide a #user field.)

                   

                  Does this make sense? And/or am I misunderstanding your issue.

                   

                  --Shawn

                  • 6. Re: my use case in Tableau
                    John Zhu

                    shawn, thanks for the help. actually I was trying the way you mentioned but it does not work for me. attached is the new example along with all data. would you help me what I am missing here?

                     

                    John

                    • 7. Re: my use case in Tableau
                      Shawn Wallwork

                      John, I'll look at this new workbook tomorrow. But in the meantime try using this:

                       

                      TOTAL(SUM(ratio))*[#user]

                       

                      This should keep your 'variable' from getting sliced & diced. Does this work? I'll check in tomorrow.

                       

                      Cheers,

                       

                      --Shawn

                      • 8. Re: my use case in Tableau
                        John Zhu

                        i make the filters global and use the new aggregation, still can not solve my problem

                         

                        really looking forward to your help tomorrow, shawn!

                        • 9. Re: my use case in Tableau
                          Shawn Wallwork

                          OK, now that I finally understand (I think) I'm pretty sure you can't do what you're trying to do without using a join on at least one field. Jonathan might be able to help you craft the most efficient way to join the tables so performance doesn't take too much of a hit.

                           

                          --Shawn

                          • 10. Re: my use case in Tableau
                            John Zhu

                            yes, what I am looking for in the past is something like "variable" in programming world but apparently tableau is not designed for this kind of thinking

                             

                            I am hesitate to do table join because my counter table has already contained millions of rows across games/dates. If I join it with other targeting dimensions, say 100 browser combinations, 100 age/gender combinations, thousands geo combinations, the joined data volume will be quickly out of control. Even during analyzing I always look into one game's data but looks like Tableau will join everything together once building the initial data connection (am I right?).

                             

                            By the way, I am using mySql and Tableau 8. May I write to Jonathan directly for some help?

                             

                            thanks a lot, shawn.

                             

                            John

                            • 11. Re: my use case in Tableau
                              Jonathan Drummey

                              See the attached for one solution…you were on the right path, however it turns out there is an undocumented aspect to Tableau's selected worksheet filters, which are described in the Sharing Filters part of the manual.

                               

                              Approach

                              At this time, Tableau doesn't generally operate in terms of "let user filter for this, then jump over here and do that," it's more of an all at once operation. Therefore, the way I approached this is to think about what needs to be done to filter the browser source in such a way that results of that filter are also available for the counter data source. Like the initial set up in the Browser worksheet, I thought making that filter a targeted worksheet filter and making it global (by selecting Apply to Worksheets->All Using This Data Source) would work. However, it didn't for two reasons:

                               

                              Complication #1 - No Linking Field

                              Tableau wants to have at least one linking field available, and in this set up the measure (ratio) is generated using an entirely separate calculation in the secondary. Therefore, I created a "Linking Field" with a value of 1 in both data sources, and edited the Relationship so that was the only linking field. Note that if any field names/data types are the same in both data sources (like Date in this case), editing the relationships is critical because Tableau's default behavior will be to blend on those fields of the same name if the field from the primary is in the view (even if the link icon is clicked off).

                               

                              Complication #2 - A Selected Worksheet Filter is Context Dependent

                              It turns out that a selected worksheet filter is specific to the "context" of how the data source is used. In the case of the Brower worksheet, even though the filter is set to all worksheets, the filter only applies when that data source is the primary. When it's secondary, it doesn't work.

                               

                              When used as secondary data source, then the selected worksheet filter has to be created in the context of the blend. For the Counter jtd and Browser jtd worksheets, the filter on browserName and date was created in the Counter jtd worksheet that has the counter source primary, then the Browser jtd worksheet is also set up with the counter source as primary.

                               

                              One downside of this technique is that the blending process introduces Null as a potential value of the filters, there's no way that I know of to remove it.

                               

                              Also, if someone is reading this and still on v7, I can't think of any way to do this dynamically using v7 blending, this depends on v8's ability to filter on dimensions from the secondary.

                               

                              Jonathan

                              • 12. Re: my use case in Tableau
                                John Zhu

                                Jonathan, really appreciate for providing the expert solution. Yes, the key is making a dummy linking field among the different data sources :-)

                                 

                                Shawn, thank you a lot too for the help!

                                 

                                John