How do I use DateDiff() to give me a difference by grouping and not just for the entire dataset?

Question

I have my data grouped by provider and then by patientID, I want to find the last time a patient "interacted" with the clinic but when I use datediff it seems to give use the latest date for the entire data set instead of by group.

My attempt is as follows:

@TimeSinceLastInteraction_Initialize

global numbervar interaction := 0; 
if {Ticket.PatientID} <> previous({Ticket.PatientID}) then interaction
else interaction :=0;

This formula is placed in group heading for patientID in order to reset the value for each group and seems to be working

@TimeSinceLastInteraction

global numbervar interaction;
interaction := DateDiff("d",maximum({Ticket.TicketDate}),CurrentDateTime);

I have tried several different date fields but I always get the same, undesired result where the value returned for each grouping is the same. This makes me think I am not setting up interaction as a variable correctly. I have also tried local vs global variable to no avail.

As a small side note, I don't really understand the difference between local and global variable declarations (I usually get away with just using global) and would appreciate an explanation on their respective uses just as much as any help on my current problem!

Thanks in advance!

Solution

First, a local variable is only available for use within the one formula where it is declared. You cannot reference it anywhere outside of its formula. A global variable can be referenced by any formula (except by those in a subreport).

In your case, you don't need to use any variables at all and you really only need a single formula to get the job done:

DateDiff("d",maximum({Ticket.TicketDate},{Ticket.PatientID}),CurrentDateTime)

The key is the second parameter you're passing to the Maximum() function; it specifies that you're only looking for the max date for that particular PatientID. Without that second parameter, it would give you the max date for the entire report. You should note, though, that the value you pass as the second parameter must be a field you're using as a group in your report.