Month field names into Month field

[#1]

I have sales information by date and want to aggregate all into a single month. My columns are separated by month but I cannot get all the months to appear in a list box for all months listed.

For example:

A B C F G H

September Calls Sales October Calls Sales

Sep 1 4 2 Oct 1 7 3

Sep 2 3 2 Oct 2 5 1

Sep 3 4 0 Oct 3 5 3

I would also like to aggregate all calls into a single field and sales as well that way I can just click a month to review the sales per account. Thanks in advance.

[#2 · Accepted solution]

My first thought was Cross Table, but it may not be necessary as you have the actual date as a field. You should be able to do a load for each month in you data set and Concatenate those into one big table. Here's an example using the data you posted:

TestData:
LOAD * INLINE [
September,sCalls,sSales,October,oCalls,oSales
Sep 1,4,2,Oct 1,7,3
Sep 2,3,2,Oct 2,5,1
Sep 3,4,0,Oct 3,5,3
];
LOAD September As Date, sCalls As Calls, sSales As Sales RESIDENT TestData;
CONCATENATE LOAD October As Date, oCalls As Calls, oSales As Sales RESIDENT TestData;
DROP TABLE TestData;

You should be able to replace the Inline portion with whatever method you need to load your actual data. I modified the field names in the original data set to be different for each month and to be different than the final field values you want to keep.

Since you may want more date dimensions on this data, I would suggest adding some others to the loads.

...
LOAD September As Date, sCalls As Calls,
 sSales As Sales, 9 As Month, 2010 As Year RESIDENT TestData;
CONCATENATE LOAD October As Date, oCalls As Calls,
 oSales As Sales, 10 As Month, 2010 As Year RESIDENT TestData;
DROP TABLE TestData;

[#3]

Thank you. Works well and it allows me to create a nice graph with a trend line