Set Analysis Using Date Islands to Get Max Populated Value

[Michael_Reese]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7610.Test.zip:550:0]Hi, I have a StatusHistory table which includes the following:

Table - StausHistory:
Field - Status
Field - ChangeDate

There are two delinked Date tables which need to be used to select a To Date and a From Date. It's important to keep in mind the date filters are delinked. Based on the selected date range, I need to show a count of only the statuses that apply to the max date for each contact. Each contact may have a status on a different max date.

For instance, a patient has the following statuses and associated changedates:

PATIENT	CHANGEDATE	STATUS
PATIENT1	1/1/2010	Active
PATIENT1	2/1/2010	Hold
PATIENT1	3/1/2010	Inactive

With no date selections, 3/1 is the max change date with an Inactive status:

PATIENT	CHANGEDATE	Active	Hold	Inactive
PATIENT1	3/1/2010			1

With 12/31/09 to 1/15/10 selected, 1/1 is the max change date with an Active status:

	PATIENT	CHANGEDATE	Active	Hold	Inactive
	PATIENT1	1/1/2010	1

With 12/31/09 to 2/15/10 selected, 2/1 is the max change date with a Hold status:

PATIENT	CHANGEDATE	Active	Hold	Inactive
PATIENT1	2/1/2010		1

TXStatusCount is the fact that represents the count.

The actual pivot table does not show the patient or the date. It show's the Patient's Account Manager in Rows and Treatment Status as columns in a pivot table. I'm open to using fixed expressions instead of Status as a dimension

Could someone please steer me in the right direction with this? I'm having difficulty finding applicable documentation or examples.

Thanks!

[Michael_Reese]

As I mentioned earlier, the following expression works for the date range, but I still only need to count the value that applies to the max CHANGEDATE for each patient even though CHANGEDATE and PATIENT are not dimensions of the chart:

sum({$<[CHANGEDATE={">=$(vFromDate)<=$(vToDate)"}>} TxStatusCount)

All I would need to complete the expression above is that max value for each patient, but it just does't work. Everything I've setup keeps getting back the overall max date, not the one for each patient. Aggr has not worked either, at least not how I've had it implemented. The following expression returns nothing.

sum({$<CHANGEDATE]={"$(=aggr(max(IF[CHANGEDATE>= $(vFromDate) and CHANGEDATE<= $(vToDate),CHANGEDATE) ),CONTACTID))"}>} TxStatusCount)

I've also tried combinations of firstsortedvalue, which works if I have CONTACTID in the chart, but it doesn't aggregate correctly when using account manager as the dimension. It then only shows the first sortedvalue for each account manager even though i'm using the TOTAL <CONTACTID> qualifiers. Here it is before my failed attempts at aggregation:

firstsortedvalue(if(STATUS='Hold', TxStatusCount),-CHANGEDATE)

Plus, firstsortedvalue won't give me a column total.

[Michael_Reese · Accepted solution]

I was able to find a solution without using set. If anyone can find another solution that does the same thing using set, I'd like to see it.

=if(vFromDateCount=1 and vToDateCount=1,
count(if([Treatment Status]='Active' and [Treatment Status Date Change]=aggr(nodistinct max(if([Treatment Status Date Change]>='$(vFromDate)' and [Treatment Status Date Change]<='$(vToDate)', [Treatment Status Date Change]) ), CONTACTID),CONTACTID)),
if(vFromDateCount>1 and vToDateCount>1,|
count(if([Treatment Status]='Active' and [Treatment Status Date Change]=aggr(nodistinct max([Treatment Status Date Change]), CONTACTID),CONTACTID))