Solved

Calculated Field using IF statements - and related object (Roll-Up Summary)

  • 1 July 2020
  • 1 reply
  • 105 views

Userlevel 5
Badge +1

Is it possible to use IF statements in a calculated field, and reference a related object?

We have an object for Provisioned Services (which are basically professional service hours, purchased by the client) which are related to a Company. These have different departments, and also different Status’ (Provisioned, Fulfilled, Expired). 

Is there a way to get a roll-up summary with different slices such as; 

Customer Success Hours - Provisioned
Customer Success Hours - Fulfilled 

On the Company level? 

I am currently doing it via Rules Engine every 2 hours, using a number of transformation/pivot tasks, which seems very cumbersome. 

icon

Best answer by jean.nairon 10 July 2020, 19:26

Hi @HollySimmons  - Unfortunately there’s no way to add a formula field with a roll-up. If you created a rule to query your roll-up object and pivot by account, you have the best solution. This is a bit cumbersome and you are correct, you can only calculate as often as every 2 hours which can create some challenges for CSMs as its not real time. This can be a challenge if your team is adding Success Hours regularly throughout the day. 
 

If you are running a rule to the roll-up, you can filter your queries to get to the different slices. If there is a way in your data to filter out which hours are provisioned and which ones are fulfilled, you should be able to load the results to two separate fields. This is unfortunately the only way to get the different rollup slices. 

 

Gainsight doesn’t support If Statements currently in formula fields. However, you can achieve similar results using the query tasks. For example, if you are wanting to Roll-up Success Hours IF an Account is located in the United States, you can add this to your query. Just query the accounts and pull which ones exist in the United States and then you can do a merge between the Success Hours and the Accounts. 

Hope that example helps and gives you a sense how you could build an If Statement using the query tasks and merges. If you have a specific use case or problem you are trying to solve, I can provide more specific information that can help. 

 

I have built a lot of roll-up summary type queries in the past and I think this would be a really great idea for improving Gainsight. I have added it as a new idea here: 

 

https://community.gainsight.com/cs-ideas-21/ability-to-easily-roll-up-records-in-a-summary-calculation-38566

 

Please come vote for it. :relaxed:

View original

1 reply

Userlevel 7
Badge

Hi @HollySimmons  - Unfortunately there’s no way to add a formula field with a roll-up. If you created a rule to query your roll-up object and pivot by account, you have the best solution. This is a bit cumbersome and you are correct, you can only calculate as often as every 2 hours which can create some challenges for CSMs as its not real time. This can be a challenge if your team is adding Success Hours regularly throughout the day. 
 

If you are running a rule to the roll-up, you can filter your queries to get to the different slices. If there is a way in your data to filter out which hours are provisioned and which ones are fulfilled, you should be able to load the results to two separate fields. This is unfortunately the only way to get the different rollup slices. 

 

Gainsight doesn’t support If Statements currently in formula fields. However, you can achieve similar results using the query tasks. For example, if you are wanting to Roll-up Success Hours IF an Account is located in the United States, you can add this to your query. Just query the accounts and pull which ones exist in the United States and then you can do a merge between the Success Hours and the Accounts. 

Hope that example helps and gives you a sense how you could build an If Statement using the query tasks and merges. If you have a specific use case or problem you are trying to solve, I can provide more specific information that can help. 

 

I have built a lot of roll-up summary type queries in the past and I think this would be a really great idea for improving Gainsight. I have added it as a new idea here: 

 

https://community.gainsight.com/cs-ideas-21/ability-to-easily-roll-up-records-in-a-summary-calculation-38566

 

Please come vote for it. :relaxed:

Reply