I am connecting to a Microsoft Dynamics Solomon Accounting Database (SQL). I would like to look at a monthly account balance. Instead of one field for Acct Balance and another for Month, the database has a separate field for each Month's Balance, e.g. Acct Balance Period 1, Acct Balance Period 2, etc, where each period represents a month. Is there someway to reorganize this database in Tableau to just have one Acct Balance field and one Month field so, for example, I could look at an account balance over a time period, i.e. by month, by quarter, by year?
I've attached an example packaged workbook. The first worksheet shows all the fields. On the second worksheet I created a calculated field to show one month based on a filter. But what I really want to show is the balance for all months in columns, for example in a bar chart.
Thank you.
You'll need to reshape your data before you try to pull it into Tableau.
This article is a good starting point on some of the concepts - http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis
If you'd like to continue to connect directly to SQL, you'll either need to use Custom SQL within the Tableau connection or create a view within your database to serve the data in the re-shaped format.
