Count of Days Between Dates based on Events

[mavsman]

I am needing to find the # of days between dates (Min and Max) based on a date column and and event column. Here is an example:

I have Sales Agents that I would like to group on. In the database their ID has a start date. They also have dates associated with that start date when any activity they do happens. I have looked at the Ken Hamady formula which is great but with that you enter physical dates, i can't do that here because i'm not sure the starting and ending dates, they're based on an event. I would like to incorporate this formula into the Hamady formula eventually though.


AGENT EVENT DATE EVENT
001 2011-01-01 start
001 2011-01-03 meeting
001 2011-01-05 training
001 2011-01-11 meeting
001 2011-01-12 meeting
001 2011-01-19 end

I would like to group on the Agent then calculate the number of days between starting and ending event not counting start event as a day and not counting the event training. Also, the search date will be on the event date. So someone will choose an event date (a parameter) and it will give them the agent and number of days based on starting event date. the outcome would look something like this if they chose 01-01-2011:


AGENT EVENT DATE # DAYS
001 2011-01-01 17 days....(this did not include day one and training day)



Any help would be greatly appreciated!

 

[MCuthill]

mavsman,

Would you not sure assign Min({EventDate},{Agent}) to StartDate and Max({Event Date},{Agent}) in Ken's solution?

Since you are simply counting days, without exception, you should be able to use a simple DateDiff on this.

{@NumberOfDays} -- Place this at the Agent group-level

DateDiff("d",Min({EventDate},{Agent}),Max({EventDate},{Agent}))

Your parameter will need to apply to a Group Selection I think... Min({EventDate},{Agent}) = {?Parameter}


Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Create a formula {@null} by opening a new formula and saving it without entering anything. Then create a formula like this:

//{@eventdt}:
if {table.event} <> "training" then
{table.date} else
date({@null})

Then substitute this formula for "EventDate" in Mike's formula above.

-LB