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
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
