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!

[#2]

You can use set analysis for this functionality:

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

[Michael_Reese]

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.

[syed_muzammil]

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.

[Michael_Reese]

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!

[mihaiturturica]

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

[Michael_Reese]

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)

[Michael_Reese]

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?

[#9]

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.

[mihaiturturica]

This can be done.

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