topic Re: Set analysis help in QlikView

Set analysis help

Fri, 21 Apr 2017 13:11:51 GMT

I have problem writing set-anlaysis in correct way and hope anyone can help me here.

Here is my dataset:

Person	date	Yes/no
1	2017-01-01	Yes
1	2017-01-02	No
1	2017-01-03	Yes
1	2017-01-04	No
2	2017-01-01	Yes
2	2017-01-02	No
2	2017-01-03	Yes
2	2017-01-04	No
2	2017-01-05	No
2	2017-01-06	Yes
3	2017-01-01	No
3	2017-01-02	Yes
3	2017-01-03	No
3	2017-01-04	Yes
4	2017-01-05	No
4	2017-01-06	No
4	2017-01-07	Yes
4	2017-01-08	No

what I want to do is to get % how many persons hade yes on the latest date within the daterange 2017-01-01 to 2017-01-03

The answer here should be 67%

Re: Set analysis help

tresB — Fri, 21 Apr 2017 13:28:17 GMT

Try like:

=Count({<[Yes/no]={Yes},Date={"$(=Date(Max({<Date={'>=01/01/2017<=03/01/2017'}>}Date)))"}>}[Yes/no])

Count({<Date={"$(=Date(Max({<Date={'>=01/01/2017<=03/01/2017'}>}Date)))"}>}[Yes/no])*100

Re: Set analysis help

Fri, 21 Apr 2017 13:42:20 GMT

The result is:

Date	Person	Yes/no	expr
03/01/2017	1	Yes	1
	2	Yes	1
	3	No	1

The result should be posts yellowmarked!

I thing there should be aggr function per person somewhere...

Re: Set analysis help

Fri, 21 Apr 2017 13:44:11 GMT

It should be the latest date per person within the daterange not the latest date ever....

Re: Set analysis help

Fri, 21 Apr 2017 13:46:11 GMT

Now all the persons had a det on 2017-01-03 but if there is a fitfth person:

Person	date	Yes/no
1	2017-01-01	Yes
1	2017-01-02	No
1	2017-01-03	Yes
1	2017-01-04	No
2	2017-01-01	Yes
2	2017-01-02	No
2	2017-01-03	Yes
2	2017-01-04	No
2	2017-01-05	No
2	2017-01-06	Yes
3	2017-01-01	No
3	2017-01-02	Yes
3	2017-01-03	No
3	2017-01-04	Yes
4	2017-01-05	No
4	2017-01-06	No
4	2017-01-07	Yes
4	2017-01-08	No
5	2017-01-01	No
5	2017-01-02	Yes

Re: Set analysis help

antoniotiman — Fri, 21 Apr 2017 14:23:40 GMT

Hi Liivi,

try

Count(If(Aggr(FirstSortedValue({<date={'>=01-01-2017<=03-01-2017'}>} Yesno,-date),Person)='Yes',1))
/Count(Aggr(FirstSortedValue({<date={'>=01-01-2017<=03-01-2017'}>} Yesno,-date),Person))

Regards,

Antonio

Re: Set analysis help

Fri, 21 Apr 2017 14:39:16 GMT

Thanx @Antonio Mancini It solved my problem!!!!