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.