Date and Count Problem

[#1]

My data includes the following fields:

AccountNo

AccountType

KeyDate

AdmitDate

DischargeDate

KeyDate is tied to my calendar, and users will select the time period they want to analyze based on KeyDate.

I need to be able to do two things:

1. In a chart, count the number of accounts for each account type on any given day or range of days.  If KeyDate is >= AdmitDate AND KeyDate <= DischargeDate, then include the account in the count.  If the AdmitDate = DischargeDate, the account should be counted.  The user will also be able to select the AccountType, so this count must consider that field.

2. In another chart, show a list of every account meeting the KeyDate and AccountType criteria above.

I'm guessing this is a set analysis exercise, but I can't get the syntax right.  I'd appreciate any help you might be able to offer.

Thanks!

[Oleg_Troyansky]

Sam,

unfortunately, your problems can't be solved with Set Analysis alone... Set analysis conditions are checked once for each chart (not once per each cell), and therefore you can't compare Key Date with AdmitDate and Discharge Date within the Set Analysis.

The "right solution" should be found in the data model. Depending on the type of the link that connects your Key Date with Admit Date and Discharge Date, you should be able to add a flag to those transactions that satisfy the date condition. If you have such a flag set to 1 for all the "relevant" records, then you can use Set Analysis in the simplest way:

count({<DateFlag={1}>} AccountID)

The specific logic of adding the flag depends on your specific data structure.

best,

Oleg