9 Replies Latest reply on Mar 7, 2012 12:49 PM by Jonathan Drummey

    Showing two dimensions together in the same table

    Gareth Jones

      Hi All

      I think this is a calculation question...but here is where I'm at.

       

      I have 24 months rolling of transaction numbers (in 24 columns).  I am currently trying to count total active customers based on transactions being more than 0 in a 12 month rolling period.  Thanks to Richard in another question, I have managed to create a custom SQL connection to create 12 columns using IIF([Month1]+[Month2]+[Month3] etc. up to 12 months > 0, "Active", "Inactive".  I have created 12 of these calculations (each one staggered back by 1 month), to give me a 12 month view of each month's 12 month's rolling transaction history, to mark it as Active or Inactive.

       

      So I am left with 12 columns named similar to  "Month 1 Rolling 12 Month Active", "Month 2 Rolling 12 Month Active" and based on how my data source is updated, I will always know that Month 1 will be the current month.

       

      So, my current question on this is....how do I get a nice set of data that shows this history?

       

      If I drag more than one of these rolling 12 month calculations onto my shelves, it tries to do this (which is handy but not what i'm after):

       

      Month 1 Rolling
      Month 2 Rolling
      Drop
      ActiveActive123456

      Inactive123
      InactiveActive45678

      Inactive65

       

      When I am trying to achieve this:

       


      Month 1 Rolling
      Month 2 RollingMonth 3 Rolling
      Active4564564456456465456456456
      Inactive456456655

       

      To achieve the counts, I am dragging in my customer number as a CNT.

       

      I hope I'm explaining myself well enough!

       

      Thanks

      Gareth

        • 1. Re: Showing two dimensions together in the same table
          Jonathan Drummey

          Month 1 Rolling, Month 2 Rolling, etc. are set up as dimensions right now. Tableau draws headers for each dimension, which is why you see Active & Inactive for each. What you'll need to do is shape your data so that Month 1 Rolling, Month 2 Rolling,etc. are all members of the same dimension, with a separate dimension for the Active/Inactive.

           

          I don't feel like I have a good understanding of how your data is currently set up, posting a packaged workbook would be useful to go from here.

           

          Jonathan

          • 2. Re: Showing two dimensions together in the same table
            Gareth Jones

            Hi Johnathan

            Unfortunately I cannot post any of my data as it is extremely sensitive.  I think I can give you an idea of how my data looks however.

             

            I have a base data set that is basically Customer Number, Month 1 Transactions, Month 2 Transactions, Month 3 Transactions etc. all the way up to 24, which gives me 24 months of transaction history, with Month 24 being the latest month.

             

            I have then selected the data in my data source as a IIF(month 1 + month 2 etc. up to 12 > 0 then "active" else "inactive")

             

            That's a summary of my data basically.  I then have 12 of those selects in my data connection filling up 12 columns of historical data.

             

            Sorry if that is still vauge, I can make a dummy sheet if needed!

            • 3. Re: Showing two dimensions together in the same table
              Tracy Rodgers

              Hi Gareth,

               

              Would you be able to post a dummy sheet? I think I have an idea, but I don't want to steer you in the wrong direction if it doesn't work!

               

              -Tracy

              • 4. Re: Showing two dimensions together in the same table
                Gareth Jones

                Hi Tracy

                Thanks!

                 

                Here is a dummy sheet - basically, what I need to show, is the second table in my original post, but using the data in the format I have provided.  (There are many, many other columns and >700,000 rows of data total in my data set)

                 

                Hopefully your idea works, as my dashboard currently has too many individual calcualotions on it, and I cant plot any graphs etc.!

                 

                Thanks

                • 5. Re: Showing two dimensions together in the same table
                  Andrew Watson

                  The way your data is structured makes this difficult (I'd like to say impossible but perhaps there's a genius on this forum who can work it out). Tableau really needs a flat data structure to work with, just like an Excel pivot table. You're trying to manipulate data that's already been pivoted.

                   

                  If you can alter the data structure it's very straightforward to achieve the look you want. I've attached a copy of your Excel file with a demo of how the data could be to make it work - this is in Sheet2, sheet1 is what you supplied. I have no idea how easy it will be for you to extract your data from it's source in this format.

                   

                  Andrew

                  1 of 1 people found this helpful
                  • 6. Re: Showing two dimensions together in the same table
                    Gareth Jones

                    Hi Andrew

                    Thanks for that.  I have suspected as much, after having a week or so more experience, I have started to see that Tableau is sort of like an advanced pivot table system - which is great and does 70% of what I need - but doesn't really support data already pivoted.

                     

                    I can see how your data will work but unfortunately I am limited to using Access as a data source, and I am sitting at ~700,000 customer records - it's a bit sluggish as it is, but multiplying it that many times would be painful (plus I would had to see the macro that did that!)

                     

                    I think I'll just have to deal with stitching 12 sperate calculations together on a dashboard, and hope the client doesnt want to see any trend graphs!

                     

                    Gareth

                    • 7. Re: Showing two dimensions together in the same table
                      Jonathan Drummey

                      I've had some ideas about what hoops to jump through to make this work, but not time to look at this, I might some time later this week. I agree with you that the hardest part is that the data is already pivoted, and to do the analysis you want in Tableau it helps to start with unpivoted data.

                       

                      Jonathan

                      • 8. Re: Showing two dimensions together in the same table
                        Gareth Jones

                        Jonathan

                        I really appreciate that.  The time savings for me would be significant - so when you do have some time, let me know what you're thinking

                         

                        Gareth

                        • 9. Re: Showing two dimensions together in the same table
                          Jonathan Drummey

                          Attached is one option, using custom SQL to generate results that look very much like what Andrew had posted above. It uses a series of 24 SELECT statements that are UNION'ed together to take your wide data and make it tall, like so:

                           

                          SELECT [Sheet1$].[Customer ID] AS [Customer ID],

                            [Sheet1$].[Month1] AS [Active/Inactive],

                            "Month1" as [Month]

                          FROM [Sheet1$]

                          UNION ALL

                          SELECT [Sheet1$].[Customer ID] AS [Customer ID],

                            [Sheet1$].[Month2] AS [Active/Inactive],

                            "Month2" as [Month]

                          FROM [Sheet1$]

                          UNION ALL

                          (etc.)

                           

                          If you need additional fields in the analysis, you could get at them in a couple of ways: One would be to just add fields to each of the 24 queries here, another would be to use data blending in Tableau. You might also get really wacky and try to join the result of all the UNIONS with another table, I'm not sure whether the MS JET engine can handle that. If you're doing the first or third idea, I'd suggest adding as few as you need for this analysis, since 24 * 700,000 = 14 million rows and the smaller the data set the faster the query will run. Something I've been learning is that the speed of Tableau is often more impacted by moving data (across the network or from HD to RAM) than by the calculations Tableau does.

                           

                          Once you have this, the view is as easy as putting Month on the Columns shelf, Active/Inactive on the Rows shelf, and CNT(Customer ID) on the Text shelf.

                           

                          Jonathan

                           

                          Message was edited by: Jonathan Drummey I just realized I wasn't that clear, I'm thinking that you could add this as a separate datasource for a set of view(s), while using your other datasource(s) in other views.