Get first recorded date for each case

Question & Answer
, ,
1

Cases have many statuses in our CRM. We use statusdate which at the end of every day records the status each case was in. The thing is that it doesn’t really update it, it just records it. So a case which has been in the CRM for a year has a years worth of statusdate, with a corresponding status.

What i want is to create a calculated field which gets the first statusdate for applicantid. I will name this datecreated. So, like the name explains, i will use this to identify when a case was created. Which i will then use for further analysis

Question: How can i create a calculated field which will get the first record of date for each case?
Date - statusdate
Case - applicantid

2

@HarveyB-B, you can use a rank function to get the first occurrence of you application id status date, please refer this community post and let us know if this resolves your use case - Calculating if it is the first occurrence of an event.

3

So i have tried this, and replaced the date/caseid to my relevant naming, but removed ‘{TRANSACTION_TYPE}’ since i am not needing this in the calculated field.

What i would then expect is a statusdate (date) for every caseid (case) along with a status where the TRANSACTION_NUMBER = 1, which would be the date the casestatus was created.

However hundreds if not thousands of caseid’s aren’t being shown.

These are the main fields i am using:
statusdate - (date, but for every status per case)
applicantid - (case id)
casestatus - (status of a case)

Edit: is there not some sort of function which will do this for me? something like min(date, caseid)

4

@HarveyB-B you can use min function, similarly minover function.