5 Replies Latest reply on Sep 7, 2017 9:39 AM by Vincent Baumel

    line diagram showing duration and funding amount of several projects

    Franz Rosenberger

      Hey there.

       

      I'm completely new here and I hope someone can help me with a custom diagram I'm trying to realize.

       

      I have a table of ca. 160 projects each of which has a beginning date, an ending date and an associated amount of money.

      The diagram I need to create should have a money-scale as an y-axis whereas the x-axis shows years.

       

      In this diagram each project should now be shown as a horizontal line that extends from start to end date and sits at the corresponding money value on the y-axis.

      Overlappings might happen, I'm aware of that.

       

      As this is a bit urgent I'd be extremely grateful for some quick replies on how to achieve this.

      1000 Thanks in advance!

        • 1. Re: line diagram showing duration and funding amount of several projects
          Vincent Baumel

          Do you have a sample set of data, perhaps? Knowing what you have to work with will make a solution much easier to find. If you can attach a packaged workbook that'd be best, but a table of data would work too. This sort of chart should be a cinch to put together, if your data is manageable!

          • 2. Re: line diagram showing duration and funding amount of several projects
            Franz Rosenberger

            Hello Vincent,

            thanks for the swift reply!

             

            Of course it's a lot easier to address specific data. I'll attach my table here.

             

            There's one more dimension in there, the 'style-type' which is either 1 or 2.

            This refers to the style a project-line should be displayed in. So in the end one part of projects should be displayed as e.g. green lines whereas the rest is displayed as e.g. blue lines.

             

            Thanks for your help!

            • 3. Re: line diagram showing duration and funding amount of several projects
              Vincent Baumel

              Okay, so here's where I'm at:

               

              Your data as you've presented it needs to be pivoted, so that there is only one date field. In Tableau, click the Data Source tab at the bottom, then shift+click or ctrl/cmd+click the two date fields, right click the headers, and select "Pivot". Rename these something like "Project Status" and "Date" to keep it simple.

               

              The next thing we need to address is that there must me a way to differentiate one project from another. On the data side, if you've got some sort of Project ID field that would be ideal. I added a field to your Excel file to achieve this, which will come into play in a minute.

               

              In Tableau Desktop, let's drag Date out onto the columns shelf with the right mouse button, and select this one:

              Most of your projects are only going to last a few years, but if you want to see them in context to your entire data set we'll want the axis to remain the same. Right click the axis and select "Edit Axis". In this dialogue box you can choose a Fixed Range, which will keep the bounds of your axis static.

               

              Next we'll want to drag the Project ID field and Style-Type field up from the Measures section to the Dimensions section. Since these are numeric, Tableau will anticipate you wanting to treat them like a Measure, but it's not exactly how you want to use them this time. Funds is the only measure you're after here, so drag that out onto your Rows shelf. So far it should look something like this:

               

              This is connecting all of the data points, because we haven't given it a way to differentiate the separate projects. We can do this by dragging the Project ID field onto the Details button. Now we're starting to see the look you mentioned; horizontal lines spanning from the beginning of a project to the end of a project, at a height that coincides with the total funds for that project. It's a bit crowded towards the bottom, but if you drag Style-Type onto the Color button it helps to see what's going on.

               

              Here's what it looks like with the data you provided, plus my addition of the Project ID field:

               

               

              If this isn't what you're after, I'm sure we can figure it out. Given what you've described though, this should definitely point you in the right direction!

              • 4. Re: line diagram showing duration and funding amount of several projects
                Franz Rosenberger

                Thank you soo much Vincent!

                This definitely looks like what I expected.

                I'll try and replicate this with your instructions.

                 

                What raises some questions on first sight though is that some projects are starting at 0M.

                I reckon this is due to the scope of the y-axis. Perspectively it would be super nice if I could change the y-scope so that that no project starts (visually) with 0 funds. Also – but i suppose that's a bit more complex – it would be awesome if the scope of the funds could vary a bit throughout the y-axis so that the bottom part of the chart is a bit more stretched (vertically) whereas the upper part is a bit more compressed.

                 

                But I don't want to steal too much of your time and you already made my day .

                 

                Tableau is an amazing tool! I just found it today.

                Though it seems to be extremely complex and demands for quite some time to get into it.

                • 5. Re: line diagram showing duration and funding amount of several projects
                  Vincent Baumel

                  The funds that appear to be at zero actually start at 53,885 but the scale of the Y axis makes it appear as though they are right down at 0. This makes sense, considering it's trying to show 18.4M+ in the same graph. Tableau makes it easy to convert the Y axis to a logarithmic scale, but I'd advise against it unless your end user is VERY familiar with logarithmic scales. It can be very deceiving when your data looks evenly spread out, but in fact it's something like 50k, 100k, 200k, 500k, 1M. Whenever possible, best practice would be to keep the scale consistent to avoid misleading the end user. You could always set up a filter so that you're only looking at a certain range of funds; this way you wouldn't need to see 53k and 18.4M in the same graph. Lots of different ways to solve this.

                   

                  Don't let the expansive capabilities of Tableau scare you off. There's a ton you can do with it, but they've really designed the product to be simple enough to get going right away. We all had to start somewhere, and the community is always here to help!