Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

baylor2016
Contributor

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

Tags (1)
1 Solution

Accepted Solutions

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

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

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

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
Contributor

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

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
Contributor

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

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

mangalsk
Contributor III

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

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

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

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

baylor2016
Contributor

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

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
Contributor

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

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.

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

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

baylor2016
Contributor

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

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.