Skip to main content
simonechen
simonechen
Contributor ⭐️⭐️⭐️
March 30, 2023
Solved

Can we perform calculations on grouped fields?

  • March 30, 2023
  • 3 replies
  • 88 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?

 

 

Best answer by alizee

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

3 replies

alizee
alizeeAnswer
VIP ⭐️⭐️⭐️⭐️⭐️
March 31, 2023

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
The most expensive part of building is the mistakes. That's true in construction. Not so much in CSOps. So ask questions, make mistakes and learn. All views expressed here are my own.
    simonechen
    simonechenAuthor
    Contributor ⭐️⭐️⭐️
    April 4, 2023

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

      alizee
      alizee
      VIP ⭐️⭐️⭐️⭐️⭐️
      April 4, 2023

      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. 

      The most expensive part of building is the mistakes. That's true in construction. Not so much in CSOps. So ask questions, make mistakes and learn. All views expressed here are my own.
      Terms & ConditionsAccessibility statement