Distinct

[CB-CAN]

Hi

I am grouping birth dates into age cohorts (there are duplicate lines for individuals that I want to remove.  counting all birth dates This equation works in only counting the distinct Person ID for all the birth dates:

count({<[Person Date Of Birth]>}distinct[Person ID])  - this equation works

I can not get it to work with the age grouping equation that converts DOB to age years then age cohort, to only count the distinct Person ID to work - 

Count({<IF(Age(today(),Date(Date#([Person Date Of Birth],'DD-MMMM-YYYY'),'YYYY.MM.DD'))>85,'>85')>}distinct[Person ID])  - this equation does not work

Thanks

[MayilVahanan]

Hi

Try like below

Sum(Aggr(IF(Age(today(),Date(Date#([Person Date Of Birth],'DD-MMMM-YYYY'),'YYYY.MM.DD'))>85, 1),[Person ID]))

[vinieme12 · Accepted solution]

As below

=Count({<[Person ID]={"=Age(today(),Date#([Person Date Of Birth],'DD-MMMM-YYYY'))>85"}>}distinct[Person ID]) 

Best to create an actual age  field while loading the data to simplify the expression

Also Refer below articles

https://www.analyticsvidhya.com/blog/2014/01/set-analysis-qlikview/

https://community.qlik.com/t5/Design/The-Expression-Search/ba-p/1463907

,Age(today(),Date#([Person Date Of Birth],'DD-MMMM-YYYY')) as Agefield 

Then use the age field in expression as below 

=Count({<[Agefield]={">85"}>}distinct[Person ID]) 

[CB-CAN · Accepted solution]

Thank you for your solutions.  this solution =Count({<[Person ID]={"=Age(today(),Date#([Person Date Of Birth],'DD-MMMM-YYYY'))>85"}>}distinct[Person ID]) but I can not get it to work to find aged ranges and distinct person ID.

this works for counting within aged cohort, but not counting for individual person ID "Count(IF(Age(today(),Date(Date#([Person Date Of Birth],'DD-MMMM-YYYY'),'YYYY.MM.DD'))>75 and Age(today(),Date(Date#([Person Date Of Birth],'DD-MMMM-YYYY'),'YYYY.MM.DD'))<=85,'75<85')). What is the trick to count age cohort (65<75) and ensuring only count once for each individual.

Thanks 

[CB-CAN]

Hi 

I would like to create actual age fields and Master Items, but at this time it is not possible.  I have tried to get the equation to count unique Person ID for the age group 50 to 65 

I got this working to count age group, but it counts the duplicate entries for individuals

Count(IF(Age(today(),Date(Date#([Person Date Of Birth],'DD-MMMM-YYYY'),'YYYY.MM.DD'))>50 and Age(today(),Date(Date#([Person Date Of Birth],'DD-MMMM-YYYY'),'YYYY.MM.DD'))<=65,'50<65'))

thanks Chris

[vinieme12]

between 50 to 65 as below

=Count({<[Person ID]={"=Age(today(),Date#([Person Date Of Birth],'DD-MMMM-YYYY'))>50  AND Age(today(),Date#([Person Date Of Birth],'DD-MMMM-YYYY'))<65"}>}distinct[Person ID]) 

refer below on how to use Data load editor to create derived fields

https://www.youtube.com/watch?v=rPK-1xE6VLw

example:

Load 

Dim1,Dim2,Dim3, Age(today(),Date#([Person Date Of Birth],'DD-MMMM-YYYY')) as Agefield 

From SomeTable;