18 Replies Latest reply: Feb 23, 2015 11:26 AM by Troy Proffitt

# Need help with the Aggr function

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

• ###### Re: Need help with the Aggr function

Can you provide 5-10 lines of sample data please?

• ###### Re: Need help with the Aggr function

I'm not sure I understand what you want.  The chart on the bottom right is what I'm using for the data.

• ###### Re: Need help with the Aggr function

Here is a spreadsheet that includes the data

• ###### Re: Need help with the Aggr function

Try below

=SUM({<Month = {'\$(=Max({<Year = {'\$(=Max(Year))'}>}Month))'},ActiveEmployeeYN = {'1'}>}[Annual Salary])

• ###### Re: Need help with the Aggr function

it's showing an error in the expression window, and showing a 0 value in the text box.

• ###### Re: Need help with the Aggr function

Create an Inline Table as below

[

Month, MonthNo

Jan, 1

Feb, 2

Mar, 3

];

Upto Dec 12 and use MonthNo instead of Month...

Let me know if still not working for you.

• ###### Re: Need help with the Aggr function

Same issue,  MonthNo is still underlined in red

• ###### Re: Need help with the Aggr function

=Month = {"\$(=Max({<Year = {'\$(=Max(Year))'}>}MonthNo))"}

• ###### Re: Need help with the Aggr function

I still get an error.  I've attached the qvw.  I hope this helps

• ###### Re: Need help with the Aggr function

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

• ###### Re: Need help with the Aggr function

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

• ###### Re: Need help with the Aggr function

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

Check again whether it is working or not?

• ###### Re: Need help with the Aggr function

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?

• ###### Re: Need help with the Aggr function

WorkDate is MDYY format

Here is the code used for Master Calendar:

```/* Temporary MinMax Table */
min_max:
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:
DATE(\$(vMinDate) + ROWNO() - 1) AS TempDate
AUTOGENERATE \$(vMaxDate) - \$(vMinDate) + 1;

DROP TABLE min_max;

/* Calendar */
MasterCalendar_temp:
NoConcatenate
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
*
RESIDENT MasterCalendar_temp
where
peek( WorkDate) <> WorkDate
order by WorkDate Desc;

```
• ###### Re: Need help with the Aggr function

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

• ###### Re: Need help with the Aggr function

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

• ###### Re: Need help with the Aggr function

Check any date ending with 00

• ###### Re: Need help with the Aggr function

Like this?