4 Replies Latest reply on Jul 2, 2018 11:25 AM by A S

    Automation of dashboard using Tableau to improve efficiency

    A S

      Image

      I am trying to figure out a way to create the above graph automatically in Tableau and link it to PowerPoint so that when the data in Excel gets refreshed, the PowerPoint chart gets updated as well. Currently, the underlying chart in this photo is a 100% stacked column chart with gap width set to 0 so that there's no space between the bars for different years. However, I have had to manually place the rectangles on top of the stacked column chart and adjust the height and width so that it's spatially accurate (for example, 16% is 16% of the area of the rectangle below). As you can imagine, it's a very long and frustrating process to update the rectangles manually when the underlying data gets updated. Is there a way to automate the creation of such a chart in excel?

       

      I was thinking of creating a Marimekko chart in Tableau (which requires manipulations for the data to be in the format desired but can be done) for, let's say, FY18 and rotating it by 90 degrees clockwise. But I'm not sure how to rotate the chart without saving it as an image first. Also, by this method, I'll have to create Marimekko charts for each of the financial years (18, 19, 20 and 21) individually and then place them side by side which might reduce some labor as compared to the method I am currently using but will not be immensely helpful.

       

      Another method that I have tried without much success is overlaying the rectangles in the form of a bar graph for each year. See example below.

      Image

      The problem with this being that I have not been able to figure out a way to adjust the width between different bars in Tableau so that they overlap exactly with the underlying chart to create the desired view.

      Any thoughts on a solution to this problem will be much appreciated. I have been wrapping my head around this for a while now but haven't been able to make much headway.

        • 1. Re: Automation of dashboard using Tableau to improve efficiency
          Chris McClellan

          Can you provide some sample data and workbook ?

           

          Have you tried using labels on the bars ?

           

          In a dashboard it should be simple to arrange the graphs to align *IF* there is the same number of values on the x-axis.

           

          But I get the feeling I'm missing something, so any sort of dummy workbook to illustrate the concept should help

          • 2. Re: Automation of dashboard using Tableau to improve efficiency
            A S

            Hi Chris,

             

            Thanks for your response. I'm not sure if I understand what you mean by aligning the graphs if we have the same number of values on the x-axis. Could you please elaborate? I'm afraid the data is highly confidential; so it's not possible for me to provide sample data but essentially, I have currently created a 100% stacked column chart setting the gap width to zero and then layered the rectangles manually on top of the underlying chart (the rectangles are for displaying a proportion or percentage of the rectangles in the underlying column chart).

            • 3. Re: Automation of dashboard using Tableau to improve efficiency
              Chris McClellan

              I understand the data is confidential, but surely you can randomise the data (use the RANDBETWEEN function in Excel) to create some data so we can see. 

               

              With the same number of values on the X-axis.  It look like you're looking at 4 years,  but if one of the graphs have more years then it won't align properly.

               

              It's really hard to talk about this without a visual cue, so anything that you can provide (really, in Tableau) would be great

              • 4. Re: Automation of dashboard using Tableau to improve efficiency
                A S

                Hi Chris,

                 

                I have attached an Excel spreadsheet which contains some sample data. Also generated the underlying graph which is a 100% stacked column chart with zero gap width. The challenge is to create a graph similar to the one I posted in my original query either in Excel/Tableau. Please let me know if you have any questions.

                 

                Thanks!