Jump to content
  • 0

calculated fields and aggregate totals- grouping



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.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...