Report builder: MIN & MAX aggregation for date fields

Related products: None

Hej  Gainsight community,





I am surprised that I cannot us a MIN or MAX aggregation on date fields when using the report builder. 





Maybe I am just unable to find out how it actually works, or no one else needs such a aggregation on data fields when building reports & dashboards. But I would really appreciate to have such an option.





Please, dear GainSight peeps let me know if there is a work around or, in case this is really not possible, if you have planed to add this function.





Best regards,


Max
Hi Max,





It looks like Max/Min is available on some date fields in the report builder, but not all. I've tagged our product manager for more information.
I need this too.  I need to build a report on a specific set of CTAs (recurring) and the MAX close date to see the last time a CTA of a certain type was closed.  I cannot seem to find a way to do that in reporting.  It won't let me do MAX close date.  
Is there any update on which fields/objects MIN and MAX aggregation are available for, if at all? I'd like to create a CSTask report for the current open task in a playbook by MIN due date. This would really help our managers know where their team members are in a playbook.
I was just in search of this myself, and surprised I couldn't use MAX/MIN on date fields. My use case is to identify the most recent touch to a customer. I promised this to a member of management, and now I'm having to backtrack.





My observation (untested, and based on a very small number of spot-checks) is that you can use MIN/MAX on date fields residing in MDA objects, but cannot use MIN/MAX on date fields in SFDC objects. That makes MIN/MAX unavailable for Customer Info, Account and CTA objects.





I suppose the work-around is to write a "Load to MDA" Rule to shuttle the SFDC date-based data to the MDA, but what a hassle.
I'm guessing as Gainsight moves toward the NXT model, requests such as this won't get any more traction. Bummer as this would've been a great feature.




Hi Jeremy,





We not only welcome but love the feedback that our customers provide on the community. I assure you that we actively consider this feedback for the product roadmap and prioritization.





Our overall move towards NXT does not mean that we will not prioritize providing robust salesforce integrations. This particular problem arises from a salesforce limitation with Custom Fiscal Year - where salesforce does not provide us aggregation functions on date fields. Its quite hard for us to productize the Salesforce queries (SOQL) for all the scenarios. Let me review with the team to see if we can provide an acceptable solution for this problem.




Hi Rakesh,





I really appreciate your feedback!





The problem here is that, as admins, ee have an inconsistent experience across fields that seem to be of the same type (even as Lila said, this functionality is available on certain date fields). Then, Lila says that a PM will address this topic and then it goes dormant for 3 years from the Gainsight side. This is why it feels that basic reporting capabilities are being passed over for bigger development on a new platform.




Any update on where this is on the roadmap?




Min and Max Aggregations on date fields for reports on SFDC data is not supported in SOQL.




This is our use case as well. I need to know max due date of CTA records by account. If the min/max functionality is an issue with Salesforce date fields, why wouldn't the aggregation be available with CTA (Gainsight object) date fields?




Hi Sierra,





For Gainsight Salesforce edition customers, the objects that store CTA data are in Salesforce custom objects, so we are limited by their data rules even on Gainsight objects.





Gainsight NXT uses all MDA objects for data storage though. :)




Hi Dan,





Great to know, but still doesn't address the use case without upgrading to NXT.




Hi Sierra,





That's correct. The Salesforce query tool (something that Gainsight can't change) doesn't support MIN or MAX aggregations on date fields.




One way that I have gotten around this (and other limitations) for the last couple of years is to replicate SFDC objects into the MDA. For example, I have a copy of the CSTask object in the MDA that checks for all records that have been modified in the last day and then writes those records to the MDA and then we use that object for reporting. It is a bit of a pain trying to manage both objects but NXT didn't exist when we started down this path :-)




Hi Team.

When I am creating a rule using the SFDC Object it allows me to do Aggregations on the Date Field.

Screenshot:

 

 

But in the Report Builder with the same SFDC Object I am unable to do the Aggregations on the Date Field. Why?

Screenshot: