Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis question

Hi,

I have the below data loaded in a table and wanted to show the sum of Salary whose Birthdate is the biggest in the current selection.

Emp IDBirthdateEmp NameSalary
11105/05/1955AAAA100000
22204/04/1944BBBB1000
33303/03/1933EEEEE10
44402/02/1922CCCC100
55501/01/1911DDDD10000
66606/06/1966EEEEE9999
22202/02/1922FFFF3333

I added a Text object with this expression:

=sum({$<[Birthdate] = {

$(=(max([Birthdate]))}>} [Salary])

What's wrong with this expression cos it is only showing a '-'.... ?

Thanks in advance

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

This worked for me. Qlikview will underline this as a syntax error, but it is fine. At least in my test.

=sum({$ <[Birthdate]={'$(=Max(Date(Birthdate, 'MM/DD/YYYY')))'} >} [Salary])

View solution in original post

5 Replies
Michiel_QV_Fan
Specialist
Specialist

Your field [Salary] should not be in italic.

Therefore your set analysis is not closed ok.

You need to close the max part in with ""

=sum({$<[Birthdate] = {"=$(=(max([Birthdate]))"}>} [Salary])


                                   {"=$(=Max(Birthdate))"}



Not applicable
Author

Thanks Michiel.  It is now showing a sum, but the total is incorrect.

It is showing a sum of all the salary in the selection.  If I don't select anything, it shows 124442, which is the sum of all salary.  if I just select some rows, it will add up their salary, instead of only summing up the largest birthdate's salary.

Something is still not correct here:

=

sum({$<Birthdate] ={"=$(=Max([Birthdate]))"}>} [Salary])


Any other suggestions please?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

I suspect that you have a date formatting problem - the field Birthdate looks like a proper Date, while the function max(Birthdate) returns a number. Plus, you are missing an opening bracket before the field Birthdate, which likely causes your Set Analysis to be ignored altogether.

The corrected syntax should be something like this (typing it "in the dark"):

sum({$<Birthdate ={"=$(num(Birthdate))=$(=Max(Birthdate))"}>} [Salary])


If it's still wrong, please post an example.


cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

Anonymous
Not applicable
Author

This worked for me. Qlikview will underline this as a syntax error, but it is fine. At least in my test.

=sum({$ <[Birthdate]={'$(=Max(Date(Birthdate, 'MM/DD/YYYY')))'} >} [Salary])

Not applicable
Author

Thanks blackmon!  You saved my day!

I've slightly modified it to format the Birthdate to MM/DD/YYYY as above in the load statement, and take away the Date conversion in the expression statement and still works!

Thanks again.