Problem counting consecutive days for individual accounts

[#1]

Hi all, I have a problem which Im struggling to resolve and hope someone here can help.

Here's what Im trying to do:

The data Im getting shows the number of calls made to certain people. The information comes from a CSV file and includes the following fields: Account, Date, Dial (1 or 0 - this identifies whether the account has been called or not on that day)

What I need to do is to dynamically calculate the maximum consecutive days in which each account was called. So for example, Account 123 was called on the 1/1/2011, 2/1/2011, 3/1/2011. If the user was to select a period of 1/1/2011 - 1/2/2011 to report on then the max consecutive calls for this particular account would be 3.

However, there could be occasions where the Account is not called, in the above example being the 4/1/2011. When this is the case, the application needs to restart the count to see how many consecutive days after this "break" the Account has been called. Once all the counts are done, the report should so the highest number of days the account was called on a consecutive basis.

This cannot be done in the load process as it needs to be dynamic as the user may choose to report over a week, month, 3 months etc so needs to be calculated on the fly.

I've racked my brains out for 2 days now without any success so would be grateful for any help.

Many thanks.

[matt_crowther]

This is a tricky one to say the least, for me the issue has been around establishing a group of 'Consecutive Calls'...which I haven't been able to do; there may be a more elegant solution than the following.

The below meets your stated need: you can select an Account and a Date period to quickly see the max number of consecutive daily calls made to that Account during the Selected period - in this instance via a matrix:

In the below we can instantly see that the max number of consecutive days the Account 'MrA' has been called in Q1 2010 is 8.

The actual file is attached.

This is the only way I can think to solve this without getting even more complex, the good thing about this solution is that it's entirely front-end based.

Hope that helps,

Matt - Visual Analytics Ltd

[#3]

Thanks for the reply, it certainly points me in the right direction.

Is there anyway of doing this without displaying a matrix? Perhaps have a text box or table which displays the max number from the matrix result?

Thanks once again for your help.

[asamariaekrickl]

Hi Matt,

Did you find a solution for this at the end?
I need to count consecutive days worked by employee to see capture staff that work too much without any days off in between.