Skip to main content
simonechen
simonechen
Contributor ⭐️⭐️⭐️
Mar 30, 2023 8:18 PM UTC
Solved

Can we perform calculations on grouped fields?

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?

 

 

alizee
alizeeAnswer
VIP ⭐️⭐️⭐️⭐️⭐️
Mar 31, 2023 10:06 AM UTC

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.
View in thread

3 replies

alizee
alizeeAnswer
VIP ⭐️⭐️⭐️⭐️⭐️
Mar 31, 2023 10:06 AM UTC

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
Translate
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 ⭐️⭐️⭐️
    Apr 4, 2023 9:19 AM UTC

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

    Translate
      alizee
      alizee
      VIP ⭐️⭐️⭐️⭐️⭐️
      Apr 4, 2023 10:25 AM UTC

      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. 

      Translate
      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