4 Replies Latest reply on Jan 10, 2013 2:56 PM by Jonathan Drummey

    Automated Dynamic Groups

    Matthew Harding

      I have many transactional datasets. Creating custom groups is too manual and creating new data is possible but not desireable.  What I would like to do is as follows


      Sum all costs by company id and set each company to a grouping based on the total costs in the file.


      I would then like to be able to compare these groups in aggregate on various dimensions using time and other factors without the groupings changing because I am no longer looking at an annual view - so the grouping has to be permanently sticky.


      Sets will not allow multiple sets on the same page (essentially just a filter) and Groups require manual creation.


      I would love to be able to reconnect to a new dataset and have the groups dynamically create based on settings.


      Any advice out there?

        • 1. Re: Automated Dynamic Groups
          Robert Morton

          Hi Matthew,


          This is a great suggestion and it's a feature I've wanted for some of my own analysis of internal data at Tableau. I suggest you post this to the Ideas section of our community pages so that others may vote on it. Here's the Community Ideas page: http://community.tableau.com/community/ideas


          One thing that I've done to speed the creation of manual ad-hoc groups is to use Data Blending and follow the steps on the KB article linked below. In your case it sounds like you want to capture and persist some cohort structure within a single data set. The trick is to use data blending by duplicating your data source, so that we may fold the cohort structure back into the data source. Try the following:


          1. Create a calculated field for your total cost that will bin companies together by some coarse granularity. For example, to make bins of size 1000 it may look like this: INT(SUM([all costs])/1000) * 1000
          2. Duplicate your data source.
          3. In a blank worksheet, drag the [company id] field from your original data source on to Rows.
          4. From the duplicated data source, drag the total cost bins calculated field to Rows. Right-click on it to change it to Discrete, and place it as the first item on Rows (before [company id]). Confirm that this shows the cohort grouping you expect.
          5. Capture your cohorts into an ad-hoc group by right-clicking on your total costs bin field on Rows and choosing 'Create Primary Groups...' as described in this KB article: http://kb.tableausoftware.com/articles/knowledgebase/how-create-primary-groups-secondary-source
          6. Your original data source will have a new ad-hoc group field reflecting this structure. You can now delete the worksheet and close the duplicated data source. You will no longer need to use data blending for your cohort analysis.


          This is of course still a manual process, but it is substantially faster than creating the groups yourself within the ad-hoc group dialog. Last, I'll point out that this technique always results in a new ad-hoc group field, and will not update any existing fields. If you need to make updates, start by creating a new ad-hoc group using the technique above, and then right-click on the original to choose 'Replace References...' and point to the new ad-hoc group. You can then delete the original.


          I'd love to see this flow streamlined or made fully dynamic as you suggested. However I cannot commit to any timeframe nor can I discuss our roadmap.


          I hope this helps,


          • 2. Re: Automated Dynamic Groups
            Jonathan Drummey

            @Robert - Nice! One idea that already exists that is a variation along these lines is "Use table worksheet as data source": http://community.tableau.com/ideas/1604. Given that functionality, if we created a worksheet that had the bin and company, that worksheet could then be used as a data source for a blend back to the original data, and with Tableau v8's not even needing the linking field in the view, all sorts of analyses are possible. We can already do a Worksheet->Copy->Crosstab and then Paste it to create the new data source and it (mostly) works. Where it fails is when the pasted data gets the wrong data types set, I'm guessing it's coming in as text and the JET engine is mucking it up.


            @Matthew - Depending on the type of analyses you want to do, table calculations can also be an option. You can use an aggregation at one level (like the bins) to then affect calculations at a different level of aggregation, like "show me a monthly count of customers who had 2+ purchases within the last 3 months" A third option is to do the initial aggregation in an underlying query/view.

            • 3. Re: Automated Dynamic Groups
              Robert Morton

              Hi Jonathan,

              The "worksheet-as-data-source" idea is one we've discussed many times over the years. I personally think we can provide a much more elegant solution, and the debate continues internally. It's great to hear from customers about all of the different challenges they have for shaping and staging their data prior to performing deep analysis. Keep the feedback coming!


              • 4. Re: Automated Dynamic Groups
                Jonathan Drummey

                Hi Robert,


                I'm glad to hear you're thinking about it!