Skip to main content

I have a database, which is extracted from our CRM. We sell and install equipment. There are two key dates, which are important: sign date (i.e. when we won the job) and install date.

I have created two formula fields (‘Sign Month (fx)’ and ‘Install Month (fx)’) to give the month in YYYY-MM format. I then have an automation in place to copy these calculated fields into ‘Sign Month’ and ‘Install Month’, which are linked fields.

Finally, I have created a Summary table where I would like to roll up various metrics by month (e.g. revenue).

This is where I come unstuck and can’t figure out a way forward…

I would like to see, for example, revenue by sign month and by install month. That way, I can summarise both our sales performance (what’s signed in a given month) as well as our operational performance (what we install in a given month).

Where I’ve got to is I think I need to differentiate the months, perhaps with a prefix such as I-YYYY-MM for Install Month and S-YYYY-MM for Sign Month. I find this a little convoluted and it also means I would have to have separate tables for each (i.e. I couldn’t have a table that compares sales won to revenue installed in a month side by side).

I couldn’t find anything on this in the forum so thought I would reach out to the community first to see if this is indeed the approach to take or if there’s a better way.

Thanks!

Hmm, I tried to set this up and got confused.  Here’s what I’ve got: 

 

It seems like this does what you need but I can’t be sure.  If you could let me know where I got it wrong I’ll see what I can do!

 

Perfect, thank you. I missed that you can select the rollup source

Hey ​@spitzy,

Exactly, @TheTimeSavingCo solution is what you are looking for based on your initial description!

If I may make an additional suggestion, you might be interested in making your architecture a bit more robust as follows:

In addition to your main table (Deals, Opportunities, or Contacts - not sure what you are dealing with here) you might want to have a table called Events.

Rather than having each of Date Signed, Date Paid, and other dates as individual fields and also linked fields on your main table, you might want to avoid that and just have a link to Events.

Under Events you will be able to track as many “events as needed” with different dates and different “Event Types” (single select option on your Events table). If needed, you can then rollup this information conditionally on your main table. (more info on rollup fields here)

This is way more scalable, given that if you decide to track additional data (e.g. End Date, or Milestone 1 Date, etc) you do not need to make huge changes to your database architecture, but you would just create new Event Types for it and new records on your Events table as needed.

Hope this is useful for you or future readers 😃. Feel free to reach out if you need help setting this up! I’d be happy to show you around.

Mike, Consultant @ Automatic Nation

Reply


Terms & Conditions