Getting Age at a specific time using Set Analysis

[tchovanec]

I have an application where the user is going to select a month-year and I need to show the age of someone at that specific time. I created an island table that contains 1200 values. These consist of the total months all the way up to 100 years old. I am now trying to put set analysis on my chart to using this island table. So if the user selects June-2012 and 6 months old, I want to be able to show all users that turned 6 months old in June-2012. Another example, is if the user selects Jan-2013 and 2 years old, I want to show only people that turned 2 in Jan-2013. I tried to create the set anaylsis but I am having some difficulty. Below is what I am using to get the age from the selections.

FLOOR(((MONTHEND(MAX(ISLAND_CAL_DT.ADMIT_DT))-DOB

)/365.25)*12)

This seems to work in a chart, but not in the set analysis.

I tried

FLOOR

(((MONTHEND(MAX(ISLAND_CAL_DT.ADMIT_DT))-DOB)/365.25)*12) = P(Months)

Months is the column from my island table holds all the monnths up to 100 years old. Any help would be appreciated. Thank you.

[swuehl]

Maybe like

=concat(

{<DOB = {">=$(=monthstart(ISLAND_CAL_DT.ADMIT_DT,- Months))<=$(=monthend(ISLAND_CAL_DT.ADMIT_DT,-Months))"}>}

     USER, ',')

The idea is to limit DOB field (I assume that's a date field) to the dates between start of month and end of the month, where the month is determined by the user selected date ISLAND_CAL_DT.ADMIT_DT from which Months months are subtracted (so Months field should have a numeric value that indicates the age in months (i.e. an integer)).

USER is the field that holds the user (name, ID, what ever you want to show). Instead of the concat() function, you can use any aggregation function you want.

Hope this helps,

Stefan