topic Re: Need help with the Aggr function in QlikView

Need help with the Aggr function

Thu, 19 Feb 2015 14:35:29 GMT

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

MK_QSL — Thu, 19 Feb 2015 20:48:51 GMT

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

Re: Need help with the Aggr function

Thu, 19 Feb 2015 20:55:34 GMT

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

Thu, 19 Feb 2015 20:58:38 GMT

Here is a spreadsheet that includes the data

Re: Need help with the Aggr function

MK_QSL — Thu, 19 Feb 2015 21:13:14 GMT

Try below

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

Re: Need help with the Aggr function

Thu, 19 Feb 2015 21:46:41 GMT

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

MK_QSL — Thu, 19 Feb 2015 23:01:11 GMT

Create an Inline Table as below

Load * Inline

[

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

Mon, 23 Feb 2015 14:11:53 GMT

Same issue, MonthNo is still underlined in red

Re: Need help with the Aggr function

MK_QSL — Mon, 23 Feb 2015 14:33:43 GMT

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

Re: Need help with the Aggr function

Mon, 23 Feb 2015 14:38:05 GMT

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

Re: Need help with the Aggr function

MK_QSL — Mon, 23 Feb 2015 14:49:43 GMT

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...

Re: Need help with the Aggr function

Mon, 23 Feb 2015 14:53:46 GMT

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

MK_QSL — Mon, 23 Feb 2015 15:03:00 GMT

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?

Re: Need help with the Aggr function

MK_QSL — Mon, 23 Feb 2015 15:09:05 GMT

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

Mon, 23 Feb 2015 16:13:03 GMT

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;

Re: Need help with the Aggr function

MK_QSL — Mon, 23 Feb 2015 16:14:35 GMT

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

Mon, 23 Feb 2015 16:18:38 GMT

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

Re: Need help with the Aggr function

MK_QSL — Mon, 23 Feb 2015 16:24:59 GMT

Check any date ending with 00

Re: Need help with the Aggr function

Mon, 23 Feb 2015 16:26:35 GMT

Like this?