Skip to main content
Josh Cook a posé une question dans #Sales And Marketing
I'm trying to create a chart to illustrate specific "Activities" in the past 120 days vs. next 120 days. Creating individual reports to show this data is easy, but I've been unable to merge those reports for a simple chart. In this instance, "Activities" will be filtered to only include sales meetings.

 

I've tried adding date filters, but hardcoding dates requires constant maintenence. 

 

Is there any way to display multiple reports on a single chart? Or, does anyone have other suggestions of how to display past and future "Activities" in separate columns on a single chart?
3 réponses
  1. 10 juin 2015, 15:43

    You'll need a custom formula field on Activities in order to accomplish this with one report/chart.  To create this field go to Setup->Customize->Activities->Activity Custom Fields->Add a new formula field (text) and use this formula:

    IF (

    AND(

    ActivityDate >= (TODAY() - 120),

    ActivityDate < TODAY()

    ), "Past 120 Days",

    IF (

    AND(

    ActivityDate <= (TODAY() + 120),

    ActivityDate >= TODAY()

    ), "Next 120 Days", ""

    ))

     

    This formula will do one of three things

     

    1) Put the text "Past 120 Days" into the field if the Activity Date is greater than or equal to 120 days ago but less than today

     

    2) Put the text "Next 120 days" into the field if the Activity Date is less than or equal to 120 days from the current date but greater than or equal to today

     

    3) Put blank into the field if the Activity date is out of those ranges

     

    Now that you have the field created, you can create your Acitivities with <Your Object> report.  For your purposes, you'll want to add a field filter to exclude records where your custom formula field (created from above) is blank.  You'll also need to group the report by your your custom formula field (mine is called 120 Day Range in this screenshot):

     

    You'll need a custom formula field on Activities in order to accomplish this with one report/chart.

     

    Lastly, create your dashboard component:

     

    User-added image