• 0
Login to follow this  
checksum

calculated fields and aggregate totals- grouping

Question

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.

Share this post


Link to post
Share on other sites

0 answers to this question

There have been no answers to this question yet

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Login to follow this