Problem with Max Date and Set Analysis

[#1]

Hi all,

I'm trying to sum all of the numbers in a field for a specific month. This should be easy with set analysis, but I'm having difficulty. The field is called "PoolingMOUAllocation" and the specific month is whatever is the most recent month (and year - but that part is figured out) available. For my data, that month is is May. My problem is that I have my month data stored in a field called BillMonth, which was created at load time using the Month() function - this function stores the value as the string version of the month (to my knowledge). When I compare values in set analysis, I use the max() funciton, which returns a num variable. I have tried a couple expressions below:

'Current Pool Minutes: ' & num(sum({<BillMonth = {$(=Max(BillMonth))}}>}PoolingMOUAllocation), '###,###.')

'Current Pool Minutes: ' & num(sum({<MonthNumber = {$(=Max(BillMonth))}>}PoolingMOUAllocation), '###,###.')

FYI - "MonthNumber" is a field I created via inline load to map month names to month numbers:

MonthMapping:

LOAD * INLINE [

BillMonth, MonthNumber

January, 1

February, 2

March,3

April, 4

...

Both of the above expressions return a value of 0. Any ideas?

-Paul

[#2]

I normally use a date field in order to play with time in set analysis,  something like " Month(Max(DateField)) ", but if you only have the Month field... have your tried using MaxString instead of Max?

[#3]

Sorry, nevermind - I actually figured out a solution. Since Month() stores a dual value (both text and num), I created a new field at load time called BillMonthNum where I take num(month(Original_Input_Date)).

This allows me to compare numerical values in set analysis using the max() function.

Thanks!