Grouping by Age Range

  • 1
  • Question
  • Updated 4 years ago
  • Answered
I have a formula field, "Age Range" with this formula:

IF(VALUE(Age__c)>18,"19+", 
IF(VALUE(Age__c)>=15,"15-18", 
IF(VALUE(Age__c)>=10,"10-14", 
IF(VALUE(Age__c)>=6,"6-9", 
IF(VALUE(Age__c)>=3,"3-5", 
IF(VALUE(Age__c)<=2,"None", "None"))))))

This age range shows up great in a report, but I want to include it in a dashboard.   I have an aggregate model and want to group by this age range field, but age range doesn't show up in my groupings - it will only let me aggregate this field.  Any thoughts on why?  
Photo of Kris

Kris

  • 574 Points 500 badge 2x thumb

Posted 4 years ago

  • 1
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
This is a limitation that Salesforce imposes on its Aggregate queries.  Very frustrating. 

The only way we have gotten around this is by creating a text field that uses workflow rules to get its values from the formula field.   Its a real hack (And probably violates some clause of a salesforce contract)  but its how we've gotten this to work. 
Photo of Kris

Kris

  • 574 Points 500 badge 2x thumb
Doh!  That is frustrating....and obviously challenging my lil brain.  :-(

I created a new text field called Age_Range_Text.  I created my workflow rule:




And set the action to update my text field:




But i get notta....


Do you see what i'm doing wrong?


Thanks for all your help!!!!  I appreciate it SOOOOO much!
Kris
Photo of Kris

Kris

  • 574 Points 500 badge 2x thumb
Thanks Pat.  Because demographics are really important for our funders and donors (we're a non-profit), I've been trying to create stuff in Skuid so i don't have to always manually do it in excel.  Unfortunately, this isn't pretty to be able to send a funder, so unless y'all know of any other tricks, we will just have to do this in excel.    

Thanks for your help.
Kris
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Kris,

It sounds like you want your table ordered by age range? I'm not sure if ORDER BY plays well with Aggregate models, but it's worth a shot if you haven't tried it.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Thanks Matt.  The problem with the Order By and Aggregates is that the Ordering field has to be included in the Grouping.  Kris doesn't want a Grouping row for every age. 

I'm not giving up on this though.  I think we can produce a solution for Kris. 
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Ok, I just read through this whole thread. Can someone explain why adding AgeRange (the text field) to the ORDER BY clause doesn't produce the desired result?

Also, could Kris add a second field update to the workflow rule which would update an 'index' field?
Photo of Kris

Kris

  • 574 Points 500 badge 2x thumb
Thanks Matt & Rob.  I do have the AgeRange (text field) in the order by clause....

Matt - I'm thinking about what you're saying about the 2nd field update and think i have an idea of what you're saying and will try it....will let you know how it goes.

Thank you again soooo much!  I can't tell you how much I appreciate y'all and the support from this community!

Kris