Creating chart with aggregated data values

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
I'm creating a pie chart from a column where the number of days to resolve an issue could range from 1 to any number. In my chart, I'd like to aggregate the day values to: 1, 2, 3-5, 6-10 and More than 10. My number of days field property is numeric. I've tried creating a calculated column but am having trouble with the formula. I tried an "If" formula with a series but that didn't work. Any suggestions?
Photo of Laura Beerman

Laura Beerman

  • 214 Points 100 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
did you try a case statement?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
np,

Make a new formula text field called Issue Resolution Groups with the formula

IF(
[Issues resolution days] < =1, "  1"
[Issues resolution days] < =2, "  2"
[Issues resolution days] < =5, " 3-5"
[Issues resolution days] < =10, " 6-10"
[Issues resolution days] < =11, "11+")

Then base your chart in that field for the "series".

I have contrived the values with leading spaces to get them to sort properly.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Correction
IF(
[Issues resolution days] < =1, "  1"
[Issues resolution days] < =2, "  2"
[Issues resolution days] < =5, " 3-5"
[Issues resolution days] < =10, " 6-10", "11+")
Photo of Laura Beerman

Laura Beerman

  • 214 Points 100 badge 2x thumb

Hi there - Trying it now. I see the different clusters are on separate lines. Does that mean each one is it's own formula? If not, how do I separate [Issues resolution days] < =1, "  1" from [Issues resolution days] < =2, "  2" for example--comma? Thanks!

Photo of Laura Beerman

Laura Beerman

  • 214 Points 100 badge 2x thumb

I'm pretty literal with the help at this stage in my QB - sorry, super green!

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Sorry, I forgot the commas. It is all one formula. 


IF(
[Issues resolution days] < =1, "  1",
[Issues resolution days] < =2, "  2",
[Issues resolution days] < =5, " 3-5",
[Issues resolution days] < =10, " 6-10", "11+")
Photo of Laura Beerman

Laura Beerman

  • 214 Points 100 badge 2x thumb
Worked like a charm! You are the bomb. Thanks so much for your help today.