Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
baylor2016
Creator
Creator

How to get count for a group people age 2-3 by a dynamic date?

Hi Experts,

I am trying to get a count for a group who aged 2-4 days at a given date (user selected). The method I used below does not give me the correct number. Please advise.

= count(DISTINCT   {<

VisitDate=

,VisitDate = {"$(='>=' & Date(V_3DaysAgo) & '<=' & Date(V_SELECTED))"}

,patientID= {"=Date(V_SELECTED)-Date(DOB)<=4"}

,patientID= {"=Date(V_SELECTED)-Date(DOB)>=2"}

>}patientID)

Thanks

Longmatch

1 Solution

Accepted Solutions
sunny_talwar

I am not entirely sure that you need the age for the current period's patient or all the patient that visisted in the last three years?

May be this

='Patient visited within 3 years and age 50-55 years old: ' & count(DISTINCT {<

VisitDate = {"$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))"}

,PatientID = {"=Age(Num(v_selectedDate), DOB) >= 50 and Age(Num(v_selectedDate), DOB) <= 55"}

>}PatientID)

or this

='Patient visited within 3 years and age 50-55 years old: ' &

Count(DISTINCT {<VisitDate = {"$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))"}, YearMonth,

PatientID = {"=Age(Num(v_selectedDate), Only({<VisitDate = {""$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))""}, YearMonth>}DOB)) >= 50 and

  Age(Num(v_selectedDate), Only({<VisitDate = {""$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))""}, YearMonth>}DOB)) <= 55"}

>}PatientID)

View solution in original post

13 Replies
sunny_talwar

Not 100% sure, but may be this

='patient count:' & count(DISTINCT  {<

VisitDate = {"$(='>=' & Date(V_3DaysAgo) & '<=' & Date(V_SELECTED))"}

,patientID = {"=Num(V_SELECTED)-DOB>=2 and Num(V_SELECTED)-DOB<=4"}

>}patientID)

baylor2016
Creator
Creator
Author

Dear Sunny,

Thank you for your helps. The sample I provided might not be appropriate.

What I really want is the number of patients who visited within three days from user selected date and the age at the selected date is 2-4 days.  The DOB might not be the same for each patient, Can this be done by set analysis?

Thanks again.

Longmatch

baylor2016
Creator
Creator
Author

I created another project using your logics, hopefully I stated my questions and needs clearly.

mangalsk
Creator III
Creator III

PFA i tried , check if it is as per you expect

sunny_talwar

I am not entirely sure that you need the age for the current period's patient or all the patient that visisted in the last three years?

May be this

='Patient visited within 3 years and age 50-55 years old: ' & count(DISTINCT {<

VisitDate = {"$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))"}

,PatientID = {"=Age(Num(v_selectedDate), DOB) >= 50 and Age(Num(v_selectedDate), DOB) <= 55"}

>}PatientID)

or this

='Patient visited within 3 years and age 50-55 years old: ' &

Count(DISTINCT {<VisitDate = {"$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))"}, YearMonth,

PatientID = {"=Age(Num(v_selectedDate), Only({<VisitDate = {""$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))""}, YearMonth>}DOB)) >= 50 and

  Age(Num(v_selectedDate), Only({<VisitDate = {""$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))""}, YearMonth>}DOB)) <= 55"}

>}PatientID)

baylor2016
Creator
Creator
Author

Hi Mangal,

Thank you for your helps.

I am still trying to understand your logic, but when 201612 is selected, no one at that time is between 50 and 55, the age listbox you created shows the same results, but the number in the textbox is 9. Please see the screenshot. Thanks.

LongmatchMangal.png

baylor2016
Creator
Creator
Author

Hi Sunny,

Your solution gives the correct answers. I am still learning your logics. Why ONLY is used here?

Thank you for your helps

Longmatch.

sunny_talwar

The only function is used to be able to use set analysis to DOB field...

baylor2016
Creator
Creator
Author

Hi Sunny,

I am still having hard time of understanding your syntax below which solved my problem.

PatientID = {"=Age(Num(v_selectedDate), Only({<VisitDate = {""$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))""}, YearMonth>}DOB)) >= 50 and

  Age(Num(v_selectedDate), Only({<VisitDate = {""$(='>=' & Date(v_selectedDate3YearsAgo) & '<=' & Date(v_selectedDate))""}, YearMonth>}DOB)) <= 55"}


Qlik Age function was used and v_selectedDate and DOB were used as parameters. The conditions (>=50 and <=55) were there. What will be returned in the outside {}? PatientID?  If this is true, there is no PatientID field inside {}. Please explain. Thanks


Longmatch.