Solved

Can we perform calculations on grouped fields?

  • 30 March 2023
  • 3 replies
  • 39 views

We’re looking at tracking cycle time of objectives in specific categories.

Do do so we group the CTAs by Objective Category and are able to look at the min and max of the corresponding start and end dates.

Is there a way to do a calculation on the grouped values and calculate the number of days between the start and end days?

 

 

icon

Best answer by alizee 31 March 2023, 12:06

View original

3 replies

Userlevel 6
Badge +9

Hi @simonechen 

The formula field cannot understand the ‘min’ and ‘max’ aspect of the field in a report. It’ll only offer the non aggregated date fields in the date difference formula. 

So while you can’t do it in a report, you can achieve this with a data design before you could display it in a report. That would involve:

  • Fetch task in which you group by to determine the minimum date
  • Fetch task in which you group by to determine the max date
  • Merge task to blend both data sets
  • Transform task with a number formula field to perform (date diff) to calculate the number of days

Thank you @alizee! I’ll give this approach a try. Shame it has to be so convoluted though

Userlevel 6
Badge +9

Hi @simonechen Let me know if you need more info as you build it… I’m sure you’ll eventually get to love data designer because, yes, it’s a bit convoluted, but it’s SOOO powerful. 

Reply