I am trying to create a chart of Revenues and Expenditures. I want each data point on the chart to represent the revenue or expense for a particular month. Our Fiscal Year runs October through September. The revenue or expense data for an object is stored by month in a single record as follows:
Object | Actual01 | Actual02 | Actual03 | ... | Actual12 |
Revenue | 100 | 400 | 200 | ... | 350 |
Expense | 325 | 115 | 800 | ... | 600 |
All the Actuals for a given object are in the same record as the object. Actual01 represents the October actual amount, Actual02 represents the November actual amount and Actual12 represents the September actual amount.
I want to total the Actuals for the revenue and expense objects and display by month on a chart. I have no problem getting the totals for each month (October-September), but since they are all in the same record, I don't know how to display them to represent each month on a chart.
I'm not sure how I stumbled on to the solution, but in Query Studio, I was able to convert my List to Crosstab and then once I swapped rows and columns and renamed my Actuals to the Months they represent, I was able to get the chart to where I needed it.