Skip to main content

Im trying to create an Incoming / Closed trend report which is using two date fields (Date Created and Date Closed). Is there a way to have this report created in Gainsight? Right now we have this as two separate graph widgets.
 

 

Yes, it can be done, but not exclusively with the Report Builder. I have done this with Data Designer (DD), looking at CTA velocity, comparing Created to Closed, to determine how well the team is “keeping up” with CTAs.

Report builder doesn’t work because each record has its own Create Date and Close Date, and they’ll always be separate fields on a common record. You essentially need to create a dataset where a Date (or a Week or a Month) is identified, then the Cases created and Cases closed on that Date (or Time Period).

I followed these general steps, and am attaching a screenshot based on CTAs. Just sub in Cases, based on your need:

  1. Fetch a COUNT of Cases which were opened, grouped by Create Date.
  2. Transform 1: On the above Fetch, add a Formula field for the First Day of the Month.
  3. Transform 2: On Transform 1, Group by the newly added Formula field, using SUM of Case Count. You now have a tally of Cases opened in all months.
  4. Now you’re going to repeat these 3 steps, but for Close Date
  5. Fetch a COUNT of Cases which were closed, grouped by Close Date
  6. Transform 3: On the above Fetch, add a Formula field for the First Day of the Month.
  7. Transform 4: On Transform 3, Group by the newly added Formula field, using SUM of Case Count. You now have a tally of Cases closed in all months.
  8. Merge: Likely a Left Join, merging Transform 2 plus Transform 4, with the join on the Formula fields denoting the first day of the month.

Your final dataset will be one row per month, with the count of the number of Cases Created, and the number of Cases Closed in each month.

Note that you’ll have limited drill-down capabilities, as you “lose” the individual record details by performing these steps. However, you can get to the trending you desire.

With that as your Data Designer, you can then build the Reports you want in Report Builder.

 

Thanks @matthew_lind. I was gonna go that way. But for a simple report, having to jump through hoops and also loose drill down - thats why I just created two separate graphs and put them side by side.

Reply


Terms & ConditionsCookie settings