Apologies if this is a silly question, I am an amateur and can't figure this out.
I need to create a calculation to determine whether a new client is a repeat client. My data has a table of clients and under that a table of cases, with multiple cases possible per client. What I need to do is determine whether a new case filed today is for a client that we have previously done work for.
I have a working calculation that lets met spot repeat clients:
{ FIXED [Client ID] : COUNTD([Case ID])}The problem with this calculation is that it treats all cases as being repeat client cases once a client gets a second case, even the first one (which wasn't a repeat when we had it). What I want is a calculation that says that the first time we open a case for the client, it is not a repeat. But every case after that point for the client is a repeat. I have a DATE field which records the opening date of the case. How can I incorporate DATE into the above calculation to get this kind of result?
Perhaps something like:
{FIXED [Customer Name], [Order Date] :
IF MIN([Order Date])=MIN({FIXED [Customer Name] : MIN([Order Date])})
THEN 'First' ELSE 'Repeat' END}
Using Sample Superstore dataset, would return the following:
Substitute [Customer Name] with Client ID and [Order Date] with your date field.
