Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Calculations & Ages from Dates of birth
Frisky
<p>Hi all</p><p> </p><p>How do work out how many numbers there are of a particular category and also within an age range?</p><p>Need to be shown similar to the below...</p><p> </p><p>Category Age range 1-7 Age range 8-12 Age range 13-17</p><p>LD 23 45 43</p><p>PI 3 12 9</p><p>SL 12 24 71</p><p> </p><p>Would I work out the ages from today's date to the Date of Birth (and if so, how) or is there a better way?</p><p> </p><p>The source data does not give the ages, only the Date Of Birth. The categories are listed in the source data.</p><p> </p><p>Thanks</p>
Find more posts tagged with
Comments
BRM
<p>If you are accessing the source data with a SQL query you could do something like this...</p><pre class="_prettyXprint _lang-sql">SELECT Category , SUM(IF(DATEDIFF(CURDATE(),date_of_birth)/360 BETWEEN 1 AND 7,1,0)) AS age_range_1_7 , SUM(IF(DATEDIFF(CURDATE(),date_of_birth)/360 BETWEEN 8 AND 12,1,0)) AS age_range_8_12 , SUM(IF(DATEDIFF(CURDATE(),date_of_birth)/360 BETWEEN 13 AND 17,1,0)) AS age_range_13_17 FROM myTable GROUP BY Category</pre>
Frisky
<p>OK, but BIRT is accessing the source data by using a URL address (xml) from Open Objects. Does this make a difference?</p>
pricher
<p>Since you cannot use SQL to create new age columns based on the date of birth, you will need to do it by creating Computed Columns in your data set.<br />
<br />
Assuming the Date of Birth is a date field, an expression similar to this one will create a computed column called "Age Over 2" for value QTY where age is over 2:<br />
<br />
[font="'courier new', courier, monospace;"]if (BirtDateTime.diffDay(row["ORDERDATE"],BirtDateTime.today())/365 > 2)<br />
row["QTY"][/font]<br />
<br />
I have attached a sample report.<br />
<br />
P.</p>
BRM
<p>I am not familiar with Open Objects as a datasource. If you have the ability to transform the data before it is transferred into your report (i.e. like the SQL above would do) then you may be able to go that route. </p><p> </p><p>If you can't do this then try making three calculated fields on the raw data that returns 1 if the age [you can do this in the expression builder with BirtDateTime.diffDay(BirtDateTime.today(),date_of_birth)] is in the specified range and 0 if not, then set a table grouping in the category and an aggregate field that counts the results of your calculated fields.</p><p> </p><p>Hope that helps.</p>
Frisky
<p>OK thanks. The BirtDateTime.diffDay(BirtDateTime.today(),dataSetRow["DOB"]) option gives me at least the wrong data (-5,432 for example) so is a start. But where does the 1 & 0 go and do I specify the 3 age groups?</p><p> </p><p>I guess one of the problems is that the training didnt go anywhere near covering this type of stuff (Joint data sets, Aggregation builders etc); I'm sure its really simple as well, so I apologise in advance......</p>
BRM
<p>You do not use the 1 and 0 like I used in the SQL if you are doing this with calculated fields.</p><p> </p><p>Do the following...</p><p>1. In a calculated field of your dataset add the formula for age (the formula I posted probably has the terms reversed hence the negative number and needs to be divided by 365 since the result it returns is in days not years), i.e . BirtDateTime.diffDay(row["DateOfBirth"],BirtDateTime.today())/365</p><p> </p><p>2. add a table to the report with 4 columns</p><p> </p><p>3. Add a group to the report. In the grouping dialog select Category as the grouping variable</p><p>4. In the group header put the Category in the first column (probably it is put there automatically)</p><p>5. In the second column add an aggregate function. In the dialog select count as the aggregate function. In that same dialog select filter and set the filter condition to something like dataSetRow["age"]>=1 && dataSetRow["age"] < 7 and similarly with the other two columns.</p><p>5. Add another row to the group header for column titles</p><p>6. set the visibility of the detail row to not visible</p><p> </p><p>and you should be all set.</p><p> </p><p>Hope that helps.</p>
Frisky
<p>Ok I'm getting somewhere now but it seems to repeat 3 amounts of data but canot figure out where it's coming from. Its certainly not counting each example of each category and then arranging into ages. I still have to bear in mind that an individual in all age ranges may fall into more than 1 category. See below...</p><p> </p><p>Category Age range 1-7 Age range 8-12 Age range 13-17</p><p>LD 32 65 84</p><p>PI 32 65 84 </p><p>SL 32 65 84</p><p> </p><p>I'd also like to list the individual categories below the summary. Sounds straighforward enough but it will only list one per row, and always includes duplicate entries.</p><p>Category</p><p>LD</p><p>LD</p><p>PI</p><p>SL</p><p>PD</p><p>SL</p><p> </p><p>How do I list them consecutively and remove any duplicates? My data source from OO will only split or delimit these categories</p>
BRM
<p>I am happy to try to help further but I think you are going to need to post a .rptdesign and some sample data.</p>
Frisky
<p>This one's all good now. A case of the grouping in the wrong place....</p>
<p>Thanks though!</p>
