Need help with the Aggr function

[#1]

I am having an issue trying to get a formula I put in a textbox to match up to a sum of rows in a chart.  As you can see from the image below, my sum of rows is $13,705,160.52.

Here is the formula I'm using:

=if(Year= $(=Max(Year)) and num(Month)= $(=Max(Month)) and ActiveEmployeeYN = 1 ,[Annual Salary])

In the green textbox above, I'm using the exact same formula but add sum and formatting:

=num(sum(if(Year= $(=Max(Year)) and num(Month)= $(=Max(Month)) and ActiveEmployeeYN = 1 , [Annual Salary])),'$#,##0.##')

Can someone please tell me what I'm doing wrong?  I believe I need to use the Aggr function, but anytime I use it, my value is 0.  Thanks for your help

[MK_QSL]

After adding Month, MonthNo table, have you reload the data... I can't see any join between Master_Calender Month and Month/MonthNo table...

Please check...

[#3]

I did reload the data, and if you look at the Table Viewer, you can see the connection.

[MK_QSL]

Remove that table and try to add below line while loading master calendar..

=SubField('$(MonthNames)',';',Max({<Year = {'$(=Max(Year))'}>}Month)) as MonthNo

Check again whether it is working or not?

[MK_QSL]

Problem lies in your Master Calendar... Let me know how you have created master calendar... Can you provide the script you have used... Also how is the format of your WorkDate?

[#6]

WorkDate is MDYY format

Here is the code used for Master Calendar:

/* Temporary MinMax Table */
min_max:
LOAD
    DATE(MIN(WorkDate)) AS MinDate,
    today() AS MaxDate
RESIDENT Fact_Employee;

LET vMinDate = NUM(PEEK('MinDate', 0, 'MinMax'));
LET vMaxDate = NUM(PEEK('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
LET vCurrentDate = NUM(TODAY());
LET vCurrentDateTime = NUM(NOW());

/* Temporary Calendar */
temp_cal:
LOAD
    DATE($(vMinDate) + ROWNO() - 1) AS TempDate
        AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE min_max;

/* Calendar */
MasterCalendar_temp:
NoConcatenate
LOAD
    monthstart(TempDate) AS WorkDate,
    YEAR(TempDate) AS Year,
    MONTH(TempDate) AS Month,
    'Q' & CEIL(MONTH(TempDate)/3) AS Quarter,
    YEAR(TempDate)  & ' ' & CEIL(MONTH(TempDate)/3) as 'QuarterYear',
    DATE(MONTHSTART(TempDate),'MMM-YYYY') AS MonthYear,
    INYEARTODATE(TempDate,$(vToday), 0) * -1 AS CurYTDFlag,
    INYEARTODATE(TempDate,$(vToday), -1) * -1 AS LastYTDFlag
RESIDENT temp_cal
ORDER BY TempDate DESC;

MasterCalendar:
NoConcatenate
Load
*
RESIDENT MasterCalendar_temp
where 
peek( WorkDate) <> WorkDate
order by WorkDate Desc;

[MK_QSL]

Seems to be wrong as 4400 will give you 4th April 1900... Correct me if I am wrong !

[#8]

When I do a preview in the table viewer, it seems to be working properly?

[MK_QSL]

Check any date ending with 00

[#10]

Like this?