5 Replies Latest reply on Aug 11, 2016 8:49 AM by Tom W

    Donut Charts on Multiple Rows (aka a grid)

    Joe Proulx

      I have what is a reasonable use case, but the solution is eluding me.

       

      I have build a donut chart that looks like this:

      donut1.PNG

      I got a pat on the back, and then a simple request: "Can you just make this two rows of four instead of one row of eight?" I started to write "Sure thing", but then got hung up. Since the Ops Area Short Name is what places the donuts into unique columns, putting eight of them into four columns is a challenge.

       

      I was able to use a Rank table calc and get them on to two rows, but then they looked like this:

      donut2.PNG

      Close...but still hung up on the individual columns. So I wrote another calc that used the rank function to say "If RANK is 1 or 5, then "Column One"" and so on. My thought was that I'd put that on the columns shelf (replacing Ops Area Short Name) and it would work...but then I just got this:

       

      donut3.PNG

       

      All the little hacks that go into making the donut charts completely broke.

       

      I don't really expect anyone to recreate this in Superstore, but more or less hoping someone has tried this with success...or that there's a quick formatting change I can make to get this to work.

       

      Thanks all!

        • 1. Re: Donut Charts on Multiple Rows (aka a grid)
          Tom W

          Would you be apposed to creating two sheets, with 1-4 filtered in Sheet A and 5-8 filtered in Sheet B then adding them both to a dashboard?

          • 2. Re: Donut Charts on Multiple Rows (aka a grid)
            Madhusudhan Khambham

            Joe, I agree with Tom W solution and we should go in that route to accomplish your requirement...

            • 3. Re: Donut Charts on Multiple Rows (aka a grid)
              Tom W

              I did a similar thing to this earlier today. The context was different, I had to stack two lines on top of each other - one for 2015 and one for 2016. We're comparing similar time periods in the year but we want to show the 2016 date on the X axis. If we use the real date, we'll see 8/1/2016 and another 'column' for 8/2/2015. So, I had to create calculated field to effectively say IF YEAR(Date) = 2015 then dateadd('day',-1,Date) else Date end. This faked my date and now I ended up with one column.

               

              Using that as my inspiration, I came up with this (note the headers for the dimensions are still visible in my sample below to help with the explanation. They are obviously hidden in the final output);

               

              There are two fields driving this; ColumnNumber and RowNumber and one which isn't visible, Index.

               

              Index; Straight forward enough, it's just an index calculation so I can number my sub categories between 1 and 17.

               

              RowNumber: ([Index]%2)*-1

              > This uses the modulo (mod) operator to calculate the remainder when you divide the Index by 2. I'm multiplying by negative one to force a sort so 'Fasteners' moves up to the top row.

              > Assuming the index is sequential (which it is) we effectively end up with a series of numbers returned as -1,0,-1,-0,-1,-0 etc. Run this in Excel to test it if you want - fill numbers 1 through 16 down the rows and type in a formula = MOD(A1,2)

               

              ColumnNumber: round([Index]/2,0)

              > Straight up, divide the index by two and round the number to 0 decimal places so we force an integer and you've got yourself a sequence of 1 through 9

               

              All three fields have their table calculation partitioning set to 'Sub Category'.

               

              Then I just threw a simple pie chart in there for Profit and Sales and used the sub category as my label. I'm hoping you can use the same method to split your donuts up without screwing the data! Of course if it doesn't work, go with my earlier and significantly easier response of two sheets with filters and a dashboard.

              1 of 1 people found this helpful
              • 4. Re: Donut Charts on Multiple Rows (aka a grid)
                Joe Proulx

                Tom - I love the creativity behind this. This is definitely where my head was going when I built the Row and Column calculations, but yours is certainly more complex. That said, given that I am using donut charts (which have a bit of Tableau hackery already going on), I opted to use your first approach and just wedge two sheets into my dashboard (I'm embarrassed that I pored over the complicated attempts without thinking of that simple one). I ended up using a table calc to give me a rank, then put the donuts ranked 1-4 on the top sheet, and 5-8 on the bottom (because I wanted my highest volumes on the top row).

                 

                I'll certainly keep the complicated solution in my pocket for future reference, but will mark the two sheets as the correct answer.

                 

                Thanks!

                • 5. Re: Donut Charts on Multiple Rows (aka a grid)
                  Tom W

                  I've never gone through the process to 'hack' the donuts (because I adhere to the 'friends don't let friends use piecharts' )

                  However, it seems to me like I'd just make a pie chart and stack a white circle on the middle right? So I would need dual axis for that.

                   

                  I setup a field called 1 which just has a value of 1, then I add MIN(1) to the rows shelf twice and set it up as a dual axis. I see you've used MIN(Number of Records) to achieve the same thing. I prefer MIN(1) as it's constant and it means I can set axis ranges accordingly.

                  I kept my first mark as a pie, but I changed it from two measures to be just the sales measure, colored by region.

                  Then my second mark is a circle, colored white and sized based on preference. I added the profit ratio as a label in the middle and boom.

                   

                   

                   

                  I'm glad the first approach worked though, sometimes the simple solves are the best.

                  Either way though, if you could select a response as the correct answer to close out the thread, that would be excellent. EDIT: I see you've already done so. thanks!