Question

Report builder - why can't I select an option to calculate the Average?

  • 23 February 2024
  • 5 replies
  • 27 views
M
Rank
Userlevel 1

I need to calculate the average ICP score per platform, which seems like a simple report/calculation. However, when I group by platform, I can only select the COUNT OF option, and AVERAGE isn’t an available option. What am I missing? 

 

 

5 replies

matthew_lind
Rank
Userlevel 7
Badge +10

@marije.peters That usually occurs if the field type in the Show Me section is not a number.

Also if you’re pulling in a lookup (which you might be, based on your screenshot here), check if you’re pulling in the ID of the lookup, or the number within the lookup. That also would impact your ability to use the AVG function.

Making people their best....currently disguised as a CS Ops Architect. How can I contribute to your success?
M
Rank
Userlevel 1

Thanks a lot @matthew_lind . That’s helpful to know. I checked and I can confirm that the field type for the Show Me field is indeed not a number, but a dropdown list, so that is probably the cause of the issue. However, it is not a lookup. Is there a quick workaround for how I can still pull the reports I need and calculate the average? I will need to create different reports where we show the average ICP score for different data fields/groupings (e.g. per platform, per health score). 

romihache
Rank
Userlevel 5
Badge +5

Sorry for intruding @marije.peters  just saw this thread

Unfortunately, dropdowns are a (more limited) string type. The only workaround I can think of is to add a new numeric field in your object. And then in the rule that upserts your ICP score field do the following:
a) Apply a Transform to the dropdown label (it has to be a string, will not accept the dropdown itself as input) using a To Number fx
or
b) Use a case statement to cast your dropdown values to the corresponding number. 

You can also do a one-off rule to test and then populate the numeric field just once so you have it ready for your report.

 

A) Cast string as a number

 

Continuous improvement is better than delayed perfection
matthew_lind
Rank
Userlevel 7
Badge +10

Glad we’ve at least got a cause, @marije.peters.

@romihache is a true pro, so those solutions are both solid. Think of how often you’ll need to process those drop-downs as numbers.

If just once for this report and the drop-down list is relatively small (10 values or fewer), a Case statement would be my choice.

If you see a future where you need to do more math, or if that drop-down list is long, the new field + rule to populate may be more durable.

Making people their best....currently disguised as a CS Ops Architect. How can I contribute to your success?
M
Rank
Userlevel 1

Thanks so much both for the really helpful suggestions @romihache and @matthew_lind . Much appreciated! 

Reply


Terms & ConditionsCookie settings