Report on basic churn calculation for widget

Hey Jake!

I was not able to figure out a simpler method for what you are asking. I had a similar goal, wanting to display the Renewal Rate of each CSM in widget format. It's not as simple as it looks because of the requirements for a Widget report. As you can't have anything in the "By" section of the report, you have to do the grouping (the timeframe, Quarter or Annual for my example) before creating the report. Or if you have a field that already does that function.

I had to set up a MDA and run a rule that loaded the CSMs available amount and amount closed. Then in the MDA, I created a calculated field for the Renewal Rate per Quarter and YTD. This made creating the actual widget report much easier, at least for me.

Exactly. I want to pick your brain on your execution in the second paragraph, because I ran into an issue I was worried about from the start:

I cannot put the criteria for "Close Date" to equal 'current month', 'current quarter', 'current year', etc. - only x day of previous month, week, etc. So I cannot wipe values and load for my desired time periods. Additionally, if my identifier to load these $'s is the close date, the calculated field cannot aggregate the time periods I want. Does your solution work around this?

So I have 2 custom fields in the MDA called Fiscal Period and Calendar Year. Both have look ups to fields on the "Date Object(Static)" object. Which is the second custom MDA object I created. In this MDA, there are only 4 fields (First Day of Quarter, First Day of Year, Fiscal Quarter, and Fiscal Year). I then ran a rule where I populated these fields manually. This allows me to pull in the FY and FQ before loading data into the mda.

My bionic rule fetches data from the Opportunity in SFDC. For the FY, I have the Fiscal Year from the Opp mapped to that Calendar Year field in the custom MDA object, set as one of the unique identifiers. This allows me to push the correct $values to the correct date in the MDA. It's kind of a complicated rule to explain on Communities, so I apologize if it's not the most helpful.

But essentially this was the workaround, so that I could aggregate on the 2 time periods I wanted (Fiscal Year and each Fiscal Quarter).

I want to follow up with an additional solution I've found, Manmeet. There is a small amount of manual intervention needed...

- In my MDA table the only fields I have a currency fields (BARR, Lost ARR, Bookings ARR), and a calculation field for both churn % and net expansion %.

- In my rule load to MDA, I only load these fields with a close date >= 1/1/xxxx (the year will need to be updated on the first of each year

- This will run & load each day. The report simply uses GS Modified Date = 'yesterday' allowing you to just show your calculated field as a widget.

This still requires manual steps but an alternative solution

Glad you found a solution to your problem! Hope I was helpful but might have been just more confusing haha