Hi Andy,
You look like you had got yourself in a bit of an over complicated situation there. There was a significant bug in the INLINE load - causing the field names to be mangled.
All of the derivation of date parts is much more easily done in script, rather than joining inline tables anyway. Also, you only need the one Date field as you can derive everything else from that. The load script then simply becomes:
ExpenseData:
LOAD
Date,
Month(Date) as Month,
Date(MonthStart(Date), 'MMM-YYYY') as [Month Year],
Year(Date) as Year,
Expenses,
Act_Amt
FROM [.\Data2.xls]
(biff, embedded labels, table is Sheet3$);
You can add a whole lot of other Date derivations if you like (quarter etc.) you will find code for these on the Community forum - or in the examples I posted links to above.
For simple month to date comparison you need only two variables set:
set vMaxMonthYear = =Date(max([Month Year]), 'MMM-YYYY');
set vPriorMonthYear = =Date(addmonths(max([Month Year]), -1), 'MMM-YYYY');
And then the expressions for the current month and prior month columns are simply:
Sum({<[Month Year]={'$(vMaxMonthYear)'},Year=,Month=,Date=>}Act_Amt)
and
Sum({<[Month Year]={'$(vPriorMonthYear)'},Year=,Month=,Date=>}Act_Amt)
The short coming of this is that the current month will most likely only be month to date (as you can't look into the future) and the prior month will be a full month. As all of your dates are in as day one then this is the best you can do anyway. If you had days in the data you would also want to derive the day number, in the load:
Day(Date) as Day,
Find the day for the max month:
set vMaxDay = =Day(Max(Date));
And then include that in the expressions also:
Sum({<[Month Year]={'$(vPriorMonthYear)'},Day={'<=$(vMaxDay)'},Year=,Month=,Date=>}Act_Amt)
The only thing you would need to worry about there is months with fewer days, ie. on the 28th of Feb you would probably want to compare with the 31st of Jan - not the 28th.
You then also get into seasonality and number of weekends in any given month - and things start getting a lot more complicated. I digress.
Hopefully the load script amendments, variables and expressions at the top of this post will solve your immediate issues.
I've attached a working example as well.
Regards,
Steve
