header
Solved

Widget with calculated fields (average)

  • 12 August 2020
  • 3 replies
  • 87 views

Userlevel 1

Hi guys,

This is probably pretty simple, but I’m failing to find a solution, and it’s kinda getting me crazy.

So I was asked two add two new widgets into a dashboard. These new widgets should show:

  • Average ARR per CSM
  • Average #Customers per CSM

Would I, for example, be able to add a formula field that gets Sum of ARR / Count distinct CSMs? 

This sounds simple but I’m kinda blocked and overcomplicated, I believe.

 

Thanks in advance for the help!

icon

Best answer by CurtisValentine 13 August 2020, 05:38

Hi @andr_raposo

For your request, you can add formula fields to reports but I don’t think it would fully solve your situation. If you were just looking to add up the totals for ARR and total number of customers, you could easily do that in a report. 

All you would need to do is create a report to pull from the Company object and you could easily aggregate Total ARR and count the unique IDs for the Total number of Customers. Here’s how to aggregate data in a report. Once you have the data, all you would need to do is create the Summary Widget and add it to the dashboard. 

But that would only really solve part of your calculation. To calculate the denominator and then be able to average the numerator, you will need to count the total number of active CSMs in your Gainsight. This adds a bit of complexity. 

What I would suggest is creating a rule and create tasks to pull from both the Company object and the User object: (1a) ARR on the Company object or (1b) number of customers from the Company object, and (2) number of distinct CSMs from the User object. 

Once you have created the tasks to query the data and sum it up, you can use a formula task to create each formula: 

  • Average ARR per CSM
  • Average #Customers per CSM

What I would do next is load the data to a new MDA object called something like KPIs. The reason I would recommend loading this data to a new table is so that you can track the trend. You didn’t ask for this but I have often seen companies like to see this kind of data on a trend line and be able to analyze how it has changed over time. Once you have the data loaded to the object, you can create the report as I described above with the Summary Widget. 


Hope this gives you a solution that provides you a bit more than what you were looking for. 

 

 

 

 

View original

3 replies

Userlevel 4

Hi @andr_raposo

For your request, you can add formula fields to reports but I don’t think it would fully solve your situation. If you were just looking to add up the totals for ARR and total number of customers, you could easily do that in a report. 

All you would need to do is create a report to pull from the Company object and you could easily aggregate Total ARR and count the unique IDs for the Total number of Customers. Here’s how to aggregate data in a report. Once you have the data, all you would need to do is create the Summary Widget and add it to the dashboard. 

But that would only really solve part of your calculation. To calculate the denominator and then be able to average the numerator, you will need to count the total number of active CSMs in your Gainsight. This adds a bit of complexity. 

What I would suggest is creating a rule and create tasks to pull from both the Company object and the User object: (1a) ARR on the Company object or (1b) number of customers from the Company object, and (2) number of distinct CSMs from the User object. 

Once you have created the tasks to query the data and sum it up, you can use a formula task to create each formula: 

  • Average ARR per CSM
  • Average #Customers per CSM

What I would do next is load the data to a new MDA object called something like KPIs. The reason I would recommend loading this data to a new table is so that you can track the trend. You didn’t ask for this but I have often seen companies like to see this kind of data on a trend line and be able to analyze how it has changed over time. Once you have the data loaded to the object, you can create the report as I described above with the Summary Widget. 


Hope this gives you a solution that provides you a bit more than what you were looking for. 

 

 

 

 

Userlevel 1

Hi Curtis, 

 

That seems exactly what I need. Thanks for the very thorough explanation.

Will try that today! 

Userlevel 4

Good luck @andr_raposo. Let me know how it goes.

Reply