topic Re: Set Analysis question in QlikView

Set Analysis question

Thu, 12 Feb 2015 16:02:16 GMT

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 ID	Birthdate	Emp Name	Salary
111	05/05/1955	AAAA	100000
222	04/04/1944	BBBB	1000
333	03/03/1933	EEEEE	10
444	02/02/1922	CCCC	100
555	01/01/1911	DDDD	10000
666	06/06/1966	EEEEE	9999
222	02/02/1922	FFFF	3333

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

Re: Set Analysis question

Michiel_QV_Fan — Thu, 12 Feb 2015 16:15:43 GMT

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))"}

Re: Set Analysis question

Thu, 12 Feb 2015 16:50:56 GMT

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?

Re: Set Analysis question

Oleg_Troyansky — Thu, 12 Feb 2015 17:14:20 GMT

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!

Re: Set Analysis question

Anonymous — Thu, 12 Feb 2015 17:14:25 GMT

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])

Re: Set Analysis question

Thu, 12 Feb 2015 17:35:48 GMT

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.