I'm trying to create a report toat create totals gropus of data from a calculated field of a single column. So far I'm struggling on how to do this.
Basically, I want to make a report, that displays a break down of age groups based on the responses received/recorded in a certain date range. the date of the response is joined to the table of users, and I'm just viewing response dates and age of the responder.
I'd like to run the report for a date range (say 30 days), then get the count of returned ages for each grouping:
(exmple report)
20-30: 25 respondents
30-40: 15 respondents
40-50: 8 respondents
total 48
I can get the count of records returned for the total. But I need to take the ages and group them into 10 year chunks, like 20-30, 30-40, 40-50, etc...
I can calculate the age of each with a calculated field, but now how to I total up those ages falling into each grouping? I can't find a way to run an Aggregation on a calculated field. Also, looking at a different post on the forums, I found some more advanced formulas for the Aggregation element. I was hoping to create an aggregate for each age range with a formual like this:
COUNT(case when Calculated_Age>19 and Calculated_Age<31 then 1 else NULL end)
Any thoughts if this can be done, or if a different approach could yeild the results I'm looking for?
Going forward I would also like to combine other data into the report, also grouped in simular fashion, maybe string based instead like State\location. Basically breaking the same date range data set down by state, not dependent on age. If there's something that works for age because its a number, but won't work for strings, I'll need to take that into consideration.
You can post now and register later.
If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.
Question
checksum
Hi All,
I'm trying to create a report toat create totals gropus of data from a calculated field of a single column. So far I'm struggling on how to do this.
Basically, I want to make a report, that displays a break down of age groups based on the responses received/recorded in a certain date range. the date of the response is joined to the table of users, and I'm just viewing response dates and age of the responder.
I'd like to run the report for a date range (say 30 days), then get the count of returned ages for each grouping:
(exmple report)
20-30: 25 respondents
30-40: 15 respondents
40-50: 8 respondents
total 48
I can get the count of records returned for the total. But I need to take the ages and group them into 10 year chunks, like 20-30, 30-40, 40-50, etc...
I can calculate the age of each with a calculated field, but now how to I total up those ages falling into each grouping? I can't find a way to run an Aggregation on a calculated field. Also, looking at a different post on the forums, I found some more advanced formulas for the Aggregation element. I was hoping to create an aggregate for each age range with a formual like this:
COUNT(case when Calculated_Age>19 and Calculated_Age<31 then 1 else NULL end)
Any thoughts if this can be done, or if a different approach could yeild the results I'm looking for?
Going forward I would also like to combine other data into the report, also grouped in simular fashion, maybe string based instead like State\location. Basically breaking the same date range data set down by state, not dependent on age. If there's something that works for age because its a number, but won't work for strings, I'll need to take that into consideration.
Link to comment
Share on other sites
1 answer to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.