Check out what's new in Gainsight's 6.3 May Release!

Hello Gainsight,

Some great news there, esp. about being able to build a custom fields directly in Report Builder!

What I find rather disappointing though is that an ability to do aggregate calculations in Report Builder is still missing in this release. Drawing from our own experience, this means being bound to undergo very painful workarounds. Personally, I consider this to be, by far, the most crucial feature not supported by GS reporting.

Do you have any estimate on when aggregate calculations can join GS Admins' arsenal?

Thanks,

Michal

Hi Michal,

Can you share an example of the type of aggregate calculation you are trying to do? We do support some types of aggregations in reporting and maybe we can help you accomplish this with the current set of capabilities.

Hello Dan,

Thank you for you reply!

For instance, it'd come extermely handy when calculating net retention in our case. Let me give you an example with a dummy data set.

Consider two subscriptions:

subsription_id | value_original | value_renewal01 | €1000 | €002 | €1500 | €2000------------------------------------------------------------------------

01 | €1000 | €0

02 | €1500 | €2000

Then, in the visualisation, we'd like to calculate net retention % as:

100*SUM(value_renewal)/SUM(value_original) = 100*2000/2500 = 80%

Obviously, calculating this for each record and aggregating (via average) doesn't work.

Any ideas how to do this in the current set up with the original data set, i.e. with the ability to drill-down?

Hi Michal,

You can do this today with formula fields and column level aggregation.

Create the formula field as follows:

Set a columnn calculations aggregation in the field setup window:

The finished result is a report that shows the percent for each row, as well as an overall average:

And if you further want to analyze aggregations by cohort group (like by CSM for example) you can enable row grouping and you'll get a report that looks like this:

Hello Dan,

I very much appreciate that you looked into this.

I'm afraid this doesn't result in correct results though. The column aggregation simply averages the result percentages under each row, which is an incorrect (arithmetic) average to use. The correct one would be weighted average, or similarly

100*SUM(Open Upsell Opprtunity Est. ARR)/SUM(ARR)

So, I take it is not currently possibly otherwise than calculating this aggregation in a seprarte MDA?

Thanks,

Michal

Hi Michal,

That's a good callout. The current formula fields capabilities in reporting do not do a weighted average calculation if you want to aggregate percentages.

@rakesh - can you think of another potential solution here?

What we released is a phase 1 of formula fields capabilities. One particular theme here is that the current release contains formulae which work perfectly in a single row scenario. The usecase Michal refers to is what would call Window functions or multiple rows formula. We do not have an exact ETA on this yet but it is on the medium term roadmap. I can post here once we have picked this up.

PS: Bionic Reporting's formula capabilities should be powerful enough to solve this.

Great, thanks for that info, Rakesh!

Looking forward to hearing more about it, since – as mentioned – this is an essential feature for multiple use cases.

Thanks,

Michal