Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ciara
Creator
Creator

Use 1 date filter for 2 tables with different dates

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

11 Replies
CurtDaughtry
Partner
Partner

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
Contributor III
Contributor III

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

Have you tried this?

Ciara
Creator
Creator
Author

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

Ciara
Creator
Creator
Author

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

CurtDaughtry
Partner
Partner

It may help to post your set analysis attempts.

Ciara
Creator
Creator
Author

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

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

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
Creator
Creator
Author

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