# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
MVP

## 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)

13 Replies
MVP

## 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)

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

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.

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

MVP

## 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)

Contributor

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

Hi Mangal,

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.

Longmatch

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?

Longmatch.

Highlighted
MVP

## 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...

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.