topic Set Analysis Using Date Islands to Get Max Populated Value in QlikView

Set Analysis Using Date Islands to Get Max Populated Value

Michael_Reese — Mon, 19 Jul 2010 16:00:57 GMT

[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!

Set Analysis Using Date Island to Get Max Populated Value

Mon, 19 Jul 2010 18:24:35 GMT

You can use set analysis for this functionality:


=only({<ChangeDate={"$(=max(ChangeDate))"}>}Status)

Set Analysis Using Date Island to Get Max Populated Value

Michael_Reese — Mon, 19 Jul 2010 20:19:01 GMT

Thanks for your response. ChangeDate will not be a filtered value. There are two delinked date fields in separate delinked tables: ToDate and FromDate. The max ChangeDate needs to fall between these two selected values.

Set Analysis Using Date Island to Get Max Populated Value

syed_muzammil — Tue, 20 Jul 2010 09:13:27 GMT

Hi,

You can use GetFieldSelections(Todate) and GetFieldSelections(Fromdate) to get the selected todate and fromdate and then compare it with Change date in the expression. GetFieldSelections() can be used in the expression.

Set Analysis Using Date Island to Get Max Populated Value

Michael_Reese — Tue, 20 Jul 2010 15:21:05 GMT

Syed, can you help me understand how this would be used in returning the value at the top of the date range for each contact?

Thanks!

Set Analysis Using Date Islands to Get Max Populated Value

mihaiturturica — Tue, 20 Jul 2010 16:20:49 GMT

Hello,

In attach you will find an example with autogenerate.

Select in Date field values:From Date ... To Date.

The result will be like in your examples.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0677.max-folder.rar:550:0]

Regards,

Mihai

Set Analysis Using Date Islands to Get Max Populated Value

Michael_Reese — Tue, 20 Jul 2010 19:26:36 GMT

Here is an example that correctly uses the date range variables. All it needs now is the incorporation of the max CHANGEDATE for each patient. This applies to the example I attached to the original post.


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

Set Analysis Using Date Islands to Get Max Populated Value

Michael_Reese — Wed, 21 Jul 2010 02:02:09 GMT

I'm almost convinced this can't be done with QlikView, especially when you take into acocunt Patient and CHANGEDATE aren't actually dimensions in the chart.

Anyone have any other thoughts?

Set Analysis Using Date Islands to Get Max Populated Value

Wed, 21 Jul 2010 15:32:02 GMT

Everything is doable.

Set Analysis can be applied for whole chart object only. If you need individual approach for each row in a chart you should use IF conditions.

Set Analysis Using Date Islands to Get Max Populated Value

mihaiturturica — Wed, 21 Jul 2010 15:51:16 GMT

This can be done.

I'm agree with Nick Bor, use Set Analysis or IF condition or both .

Re: Set Analysis Using Date Islands to Get Max Populated Value

Michael_Reese — Wed, 21 Jul 2010 16:41:52 GMT

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.

Set Analysis Using Date Islands to Get Max Populated Value

Michael_Reese — Thu, 22 Jul 2010 20:40:58 GMT

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