Use 1 date filter for 2 tables with different dates

[Ciara]

I'm sure this question might have already been answered but I can't seem to find exactly what I'm looking for.

I just want to create 2 straight tables that will give me figures for 'Number of Admissions per Specialty' and 'Number of Discharges per Specialty' for a specific date range.

I want to use the same date filter however but number of admissions will be based on the AdmitDate and number of discharges will be based on DischargeDate.

Not sure how to go about this.  Any help is appreciated.

Thanks

Ciara

[CurtDaughtry]

You need to use set analysis in your expressions. They will each look at different dates based on what you present to the user.

Example below (syntax not accurate; just directional for reference)

sum({<SelectionDate=AdmissionDate>} Admissions)

sum({<SelectionDate=DischargeDate>} Discharges)

[Diter]

Hi Ciara it looks like you have to make an association between 2 table dates

Have you tried this?

[Ciara]

I've played around with set analysis before posting my query.  Nothing I've tried has worked.  Doesn't return any results unfortunately.

[Ciara]

I'm afraid I don't know how to do this association.  Can you point me in the right direction? Thanks

[CurtDaughtry]

It may help to post your set analysis attempts.

[Ciara]

Just the regular set analysis.  

Count({<AdmitDate={'AdmitDate'}>}Distinct AccountNumber)

Also tried:

Count({<GetFieldSelections={'AdmitDate'}>}Distinct AccountNumber)

I think I need to somehow bridge both the AdmitDate with the DischargeDate.  Currently the AdmitDate is what my Master Calendar is built on.

So I want a date range picker just based on a calendar date I guess instead of one of the defined date fields. 

The Admissions table shows me all the admissions for that range and the discharges table shows me all the discharges for that range.

[CurtDaughtry]

You could do it that way. Create a new table of dates. Create a field in that table called "cal.Date" with all the dates you intend to provide.

Then you could use this as your expression.

Count({<AdmitDate={">=$(=min(cal.Date)) <=$(=max(cal.Date))"}>}Distinct AccountNumber)

This essentially will get the lowest value and the highest value of cal.Date from the selection and match it with AdmitDate.

Do the same for the DischargeDate formula and you will be able to compare on the same basis of time.

There are other techniques as well of course. Here is one of the masters' approaches.

https://qlikviewcookbook.com/list-recipes/#squelch-taas-accordion-shortcode-content-18

[CurtDaughtry]

You may need to clear the DischargeDate selection as well; in which case, the formula would be as follows.

Count({<AdmitDate={">=$(=min(cal.Date)) <=$(=max(cal.Date))"}, DischargeDate= >}Distinct AccountNumber)

[Ciara]

The tutorial link you sent was very useful. Thank you.

I'm running into one problem however.  Because both of my dates are contained in the same table, I'm getting a synthetic key when following the tutorial.  The tutorial is based on different dates contained in different tables.  Just wondering if you had any thoughts on how to rectify?

Thanks so much for your help.

Ciara