Group by using numeric and calculated fields in reports

  • 3
  • Question
  • Updated 3 months ago
  • Answered
Photo of Jahnavi

Jahnavi, Champion

  • 2,448 Points 2k badge 2x thumb

Posted 6 months ago

  • 3
Photo of Sai Ram Pulluri

Sai Ram Pulluri, Official Rep

  • 13,270 Points 10k badge 2x thumb
Hi Jahnavi, For the second part of the question are you referring to Formula fields in Reporting, here is the similar ask.

For the First part of the question, could you please share us the use-case,where you need a numeric field in Groupby.

Since its a new enhancement request, changing this to an idea post.
Photo of Jahnavi

Jahnavi, Champion

  • 2,448 Points 2k badge 2x thumb
I was looking at  a use case to count number of customers that fall in the range of 100-200 for metric x
Photo of Ben

Ben, Champion

  • 8,312 Points 5k badge 2x thumb
Right, this would be similar to "bucketed fields" in standard SFDC reporting. You can define a range of values to bucket accounts into and then compare that against a different value on the y axis. SF DC reporting is gross (non lightening/wave), so it would be nice if this was doable in Gainsight.
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,952 Points 20k badge 2x thumb
Hi Jahnavi, 

If I understand your use case correctly, you want to show the number of customers for which the value of a given metric is 100-200, right?

You can do this by setting the account name field in the report as an aggregation (count distinct) and in your filters, apply these two with an and condition linking them: "metric > 99" and "metric < 201".

This should give you a number of customers where that condition is met. 
Photo of Jahnavi

Jahnavi, Champion

  • 2,448 Points 2k badge 2x thumb
Thanks Dan, I guess this would work for a widget but not a bar chart right?
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,952 Points 20k badge 2x thumb
You could show this data using a bar chart visualization if you add a field to the "By" section to give you more than one numerical value to report on. 

So, for example you could apply the same logic: setting the account name field in the report as an aggregation (count distinct) and in your filters, apply these two with an and condition linking them: "metric > 99" and "metric < 201". And add a field like "Region" to the "By" area, for example. 
Photo of Jahnavi

Jahnavi, Champion

  • 2,448 Points 2k badge 2x thumb
I have a calculated field say ABC which is nothing but some percentage. I would like to count number of customers and group by ABC. This should automatically prepare ranges and count the number of customers in each range
Photo of Olavo Zapata

Olavo Zapata

  • 562 Points 500 badge 2x thumb
Hi Dan,

Seeing your explanation to Jahnavi, We need to find a workaround to use a formula or a field number in reports?
I have different use cases that I tried to use a formula field, for example, A Region field that is defined in a Case Formula based on states and any others.

My question is, this is a bug that Gainsight are looking for to solve or it's working as expected?

Thanks
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,920 Points 20k badge 2x thumb
I'm still not following this exactly. Can you show an example (using Excel or another charting tool) of how you'd like the output to look?
Photo of Olavo Zapata

Olavo Zapata

  • 562 Points 500 badge 2x thumb

Hi Dan,

I'll take another example but that's pretty much the same case:

Here I would like to get all accounts and group by Billing Cycle which is a number field (but could be a formula field)

And with this, input a percentage of each type by new accounts per month.

My fields are:

Col A = Account
Col B = Billing Cycle
Col C = Contract date


So, from here I would like to query them like this:

QUERY (A: C; "Select C, B, Count (A) where C is not null Group by C, B"; 1)

Out of the Gainsight is simple to do, the job is always getting information extracted.

An alternative is to create the report directly in Salesforce that would look something like this.


But in our organization, few CS are aware of Salesforce. Ideally, would be the Gainsight create these charts.
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,920 Points 20k badge 2x thumb
I understand what you need now, Olavo. Thanks for showing the detail.

You can do this in Gainsight by moving the "Billing Cycle" field to the "By:" area of the report.

Photo of Olavo Zapata

Olavo Zapata

  • 562 Points 500 badge 2x thumb
Dan This is exactly what I want! uhauAHua

but Billing Cycle is a number field and is not allowed to do it.

My point here from the beginning is if Gainsight is looking to enable to do it with Number and Formula Fields.

Thanks for your attention.
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,920 Points 20k badge 2x thumb
Ah, sorry. My mistake. You are correct that field types of number are not currently supported in the group "by" area. I've sent a note to the product owner to get clarification and input on this.