4 Replies Latest reply on Aug 6, 2013 8:40 AM by Ryan Major

    Display Budget Info on one Continuous Axis

    Ryan Major

      I am new to Tableau and I would like to create a chart that shows our budget info month by month on one continuous axis. I would like the month to be on the x-axis and the budget amounts to be on the y-axis. I am having issues with this because of how the table is set up. Below is the table structure. It includes the account, the budget year, and then the budget amounts for each month (B01-B12). I would ideally like to tell Tableau that B01 is the January budget, B02 is the February budget, etc so I can have all of the monthly budget information on one continuous axis.

       

      Account NumberYearB01B02B03B04B05B06B07B08B09B10B11B12
      ###-###-###-###-###2013$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

       

      I know the table isn't set up in an ideal way, but is there some kind of calculated field I can create to accomplish this? Any help is greatly appreciated.

       

      Thanks,

      Ryan

        • 1. Re: Display Budget Info on one Continuous Axis
          Joshua Milligan

          Ryan,

           

          As you noted, the table is not in a good format.  Ideally, each month should have it's own row with a single column for the budget value.  Tableau does have a data reshaping tool for Excel that would work really nicely in your case.  You can get it here:Installing the Tableau Add-In for Reshaping Data in Excel | Tableau Software

           

          Another option would be to edit your connection and switch to using Custom SQL.  The idea would be to union together a series of select statements to give you a row for each B00 column.  It would look something like this:

           

          SELECT [Sheet1$].[Account Number] AS [Account Number],

            [Sheet1$].[B01] AS [Amount],

          'January' AS [Month],

            [Sheet1$].[Year] AS [Year]

          FROM [Sheet1$]

           

          UNION ALL

           

          SELECT [Sheet1$].[Account Number] AS [Account Number],

            [Sheet1$].[B02] AS [Amount],

          'February' AS [Month],

            [Sheet1$].[Year] AS [Year]

          FROM [Sheet1$]

           

          UNION ALL

           

          SELECT [Sheet1$].[Account Number] AS [Account Number],

            [Sheet1$].[B03] AS [Amount],

          'March' AS [Month],

            [Sheet1$].[Year] AS [Year]

          FROM [Sheet1$]

           

           

          After you connect you could create a calculated field [Date] for the axis.  The code would look like this:

          DATE([Month] + '/01/' + STR(Year))

           

          Or you could build the date in the Custom SQL.

          I hope that one of those options helps!

           

          Regards,

          Joshua

          • 3. Re: Display Budget Info on one Continuous Axis
            Joshua Milligan

            You're welcome Ryan!  I'm curious, which option did you go with?

            • 4. Re: Display Budget Info on one Continuous Axis
              Ryan Major

              We created a new data source for our Tableau trial but the correct table wasn't added.  The table I was using was a summary table used for comparison purposes. Our database administrator was on vacation the day I made the post and I didn't realize there was another table we could add that is structured better. I tried option one but we are running Excel 2003. I would have gone with option 2 though.

               

              Thanks,

              Ryan