Need a set modifier suggestion

[ccdqlik1]

Qlik Community:

We have a Qlikview pivot table to show the N and % of people who have taken and passed qualifying tests.  The dimension in the table is the number taken (NumTaken). The user is allowed to select a time period (one or more months; or one or more years).  The goal is for the pivot table to summarize the results using only the most recent record for each person in the selected time period.  People may appear more than once in the data set and the selected time period because they have taken the qualifying tests either one at a time or they have done retakes to achieve a passing status.  Below is a sample data set.

Example with current user selection:  Year=2016

IDNUM	StatusDate	NumTaken	NumPassed
1	2016-01-06	2	1
2	2016-02-16	2	1
2	2016-03-15	2	2
3	2016-06-18	2	1
3	2016-07-21	2	1
4	2016-08-03	2	1
4	2016-10-22	1	0
4	2016-12-07	2	2
5	2016-03-30	2	0
5	2016-05-03	2	2
6	2016-04-22	2	0

This is what we currently get in the pivot table (results based on all status dates per person and percentages add up to more than 100 in the bottom row)

MaxNumTaken	Total Takers	N Passed 0	% Passed 0	N Passed 1	% Passed 1	N Passed 2	% Passed 2
1	1	1	100%	0	0%	0	0%
2	5	2	33%	5	83%	3	33%

This is what we want to see in the pivot table (results based on each person’s most recent status date and percentages add up to 100 in each row - i.e. results based on the purple rows in the sample data set)

MaxNumTaken	Total Takers	N Passed 0	% Passed 0	N Passed 1	% Passed 1	N Passed 2	% Passed 2
2	6	1	17%	2	33%	3	50%

Below are the current expressions in the pivot table. The issue is in the set modifier on the “Passed x” columns.   

Dimension: NumTaken (labeled “MaxNumTaken”)

Expressions:

Total Takers (this one is producing the correct numbers):

COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}>}IDNUM)

N Passed 0:

COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}, NPassed = {0}>}IDNUM)

% Passed 0:

COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}, NPassed = {0}>}IDNUM) / COUNT(DISTINCT {$<StatusDate =  {"=max(StatusDate)"}>}IDNUM))

N Passed 1:

COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}, NPassed = {1}>}IDNUM)

% Passed 1:

COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}, NPassed = {1}>}IDNUM) / COUNT(DISTINCT {$<StatusDate =  {"=max(StatusDate)"}>}IDNUM))

N Passed 2:

COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}, NPassed = {2}>}IDNUM)

% Passed 2:

COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}, NPassed = {2}>}IDNUM) / COUNT(DISTINCT {$<StatusDate =  {"=max(StatusDate)"}>}IDNUM))

We feel the set modifier should be something nested so that Qlikview first selects the rows with the max status date, then selects the rows with the target value of NPassed.  Can someone please provide suggestions for correcting the set modifier? 

Thanks

[sunny_talwar]

How are you getting total takers = 6 using this -> COUNT(DISTINCT {$<StatusDate = {"=max(A_StatusDate)"}>}IDNUM)??

[Oleg_Troyansky]

HI Celeste,

it would be a lot easier to troubleshoot if you posted a small sample...

I think that your filter with the Max(Date) wouldn't work correctly with your data and with your requirements. You want to consider the "most recent record for each person", which will likely to be a different date for each different person. Since Set Analysis gets validated only once per chart, the condition:

StatusDate = {"=max(A_StatusDate)"}

will always calculate the highest available date in the whole application, not per patient.

As a side comment, this syntax is also a bit wrong - a formula within a set of double quotes and an equal sign in front is treated as the "Advanced Search condition that needs to return either zero as false or any other number as true. With that in mind, your condition is always true (non-zero). The better way of selecting the highest available date would be using the $-sign expansion:

StatusDate = {"$(=max(A_StatusDate))"}

But then, as we already determined, it wouldn't help your cause.

What I'd do instead is use a nested aggregation with AGGR() and FirstSortedValue(), in order to capture the latest available value. If you sort the FirstSortedValue by Date with a negative sign, you will get the latest available value per the set of dimensions that you specify in your AGGR. For example:

COUNT( DISTINCT

     AGGR(

               IF( FirstSortedValue(NPassed, -StatusDate) = 0, IDNUM )

               , IDNUM, NUMTAKEN)

)

Something along these lines should work. You can learn advanced uses of Set Analysis and AGGR from my lecture at the Masters Summit for Qlik. If you can't make it to one of our Summits, you can find some of this information in my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense.

cheers,

Oleg Troyansky

[sunny_talwar]

May be like attached

[ccdqlik1]

Sunny, Those are the numbers that we want.  Can you please list a couple of the expression in the post?  Thanks.

[sunny_talwar]

Expressions in the order you see them in the image

1) =Count(Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))

2) =Count({<NumPassed = {0}>}Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))

3) =Count({<NumPassed = {0}>}Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))/Count(Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))

4) =Count({<NumPassed = {1}>}Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))

5) =Count({<NumPassed = {1}>}Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))/Count(Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))

6) =Count({<NumPassed = {2}>}Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))

7) =Count({<NumPassed = {2}>}Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))/Count(Aggr(If(Max(TOTAL <IDNUM> StatusDate) = StatusDate, IDNUM), IDNUM, StatusDate))

[ccdqlik1]

Oleg,  thank you for the suggestion.  We'll try it in our document as well.

[ccdqlik1]

Sunny, that solved it.  Thank you very much!

[sunny_talwar]

Awesome