Date labeled column aggregation

[#1]

I have 3 years of monthly data by branch of sales. Each column represents a month and year (January 2009, February 2009, March 2009, etc.). I want to aggregate the months into their corresponding years so that I could see the trend of sales for a particular branch year by year or all branches year by year. I cannot find how to use the Date#() or Year() functions when the date I need is the column label.

I tried each of these but got a script error, though the date#() function did recognize the correlation for the years, but the rest of my data did not load. Thanks in advance.

[pover]

John,

If I understand correctly that the month year is text in the data source you can try using num(right(Month_Year,4)) to group your years. Though in my opinion it is usually healthier to change the data source so that is has real dates. (eg. January 2009 should be 2009-1-1). If that is not possible, you can do a complete transition from text to date format by mapping the values like below:

Month_Map:
MAPPING LOAD * INLINE [
Month_Text, Month_Num
January, 1
February, 2
];

LOAD makedate(num(right(Month_Year,4)) num(applymap('Month_Map',subfield(Month_Year,' ')))) as Date

etc...

Regards.

[johnw]

You shouldn't need a mapping table since MMMM is the format code for the full name of the month. That should let you do this:

,date(date#(YourTextDateField,'MMMM YYYY'),'MMMM YYYY') as Month
,date(yearstart(date#(YourTextDateField,'MMMM YYYY')),'YYYY') as Year

[pover]

Good one, John.

[#5]

That works only for the first month. Once I input that command into the following months, I get a message error that "field names must be unique within table."

Each table is labeled as month and year. I need those connected or correlated so that when I review 2009, all data for all months of 2009 come up. Does that make sense? Sorry I am such a noob.

[johnw]

Ah, I hadn't noticed that you had this data as columns instead of rows. So your input table looks like this?

Input:
Branch, January 2009, February 2009, March 2009, etc.
Eastern, 500, 600, 550, etc.
Western, 900, 800, 700, etc.

First thing to do is change those columns into rows:

FirstStep:
CROSSTABLE (MonthTemp, Sales)
LOAD *
RESIDENT Input
;
DROP TABLE Input
;

That should give you data that looks like this:

Branch, MonthTemp, Sales
Eastern, January 2009, 500
Eastern, February 2009, 600
Eastern, March 2009, 550
etc.
Western, January 2009, 900
Western, February 2009, 800
Western, March 2009, 700
etc.

It might look good, but our MonthTemp at this point is merely text, not an actual date field. But at least now the table is in a format where we can apply the expressions I gave:

SecondStep:
LOAD
Branch
,date(date#(MonthTemp,'MMMM YYYY'),'MMMM YYYY') as Month
,date(yearstart(date#(MonthTemp,'MMMM YYYY')),'YYYY') as Year
RESIDENT FirstStep
;
DROP TABLE FirstStep
;

[#7]

The first step worked beautifully. The second step, did not work in that there is not now an error message, just no values are available for year or month. So when I reload the script, I can select branch, month, and year but only branch populates with data; year and month are left blank.

[johnw · Accepted solution]

Well, I forgot to load the Sales in the second step. Other than that, it works fine for the test data I posted. See attached.