1

I'm trying to do a Coverage report which will filter through to a dashboard, where I look to see how many accounts for a user have had a record linked to them in a custom object. I want to have a %age gauge for the users to monitor their coverage for X period.

There is an activity logger on my deployment where the reps are logging activities, and each activity joins to an attendee list, which can have one or more people from an account added to it.

Here is the calculation I want to get to:

Account Activity Date   Activity Count Field

Account1    01/01/2013  1
            02/01/2013  1
            03/01/2013  1
            04/01/2013  1

Account2    05/01/2013  1
            06/01/2013  1
            07/01/2013  1
            08/01/2013  1
            09/01/2013  1

Account3    

Account4    

Account5    10/01/2013  1
            11/01/2013  1

Coverage                    3
Frequency                  11
Coverage %age              60%


5 accounts, 3 of them covered in period X. 60% coverage.

I've been trying to do this in the reporting suite using a Count of 1 on each Account record and on the activities record, but I can't quite get it to work.

I almost get there with a count distinct in the Account object, but I cannot get it to register a 1 where there is no activity on an account. BLANKVALUE / ISNULL doesn't seem to trigger a thing here.

Improve this question

1 Answer 1

Reset to default
1

Here's how I might accomplish this:

  1. Create a formula field on the account: "Account Activity", type "Number". The formula itself would be IF(ISNULL(LastActivityDate),0,1).

  2. Create a new Account report, grouping by owner, and displaying the average of the Account Activity field. This will yield a report like: John Doe, 0.72, Jane Smith, 0.95, Bob Charles, 0.32.

If you need something more complex than just based on LastActivityDate, a trigger is probably in order.

Improve this answer
4
  • Hm, all the activities are happening in a separate object. It is more of a case of trying to see how many of a users Accounts have been referred to by this other object, for a given date range. The thing that scuppers me is the way the reporting tool sums. Put a count distinct on the object referring to the Account, and the Account itself sounds like a solution. But as they sum up the summary groups, they behave differently. Each instance of the object referring to the Accounts does a +1 to the total, the Account keeps its total as a static 1 through the grouping levels. Really hard!
    – 44f
    Jul 24, 2013 at 10:23
  • I understand why this is happening (multiple instances of the object referring to the Account, but just one instance of Account - Salesforce rather intelligently knows not to duplicate all those counts - I want it to NOT work that way in this instance). All formulae done in the report just don't work.
    – 44f
    Jul 24, 2013 at 10:28
  • Clarification of my custom objects: 1) Activities Contains a date and picklist 2) Activity Attendees Contains Contact/Account Lookups and the Count = 1 So I am trying to find: Date range X to Y, Accounts that have Activity for User Z = 90%.
    – 44f
    Jul 24, 2013 at 12:35
  • Reformatted question.
    – 44f
    Jul 29, 2013 at 12:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .