X Days ago, based on the oldest record

Q&A
, ,
1

I am creating an arrears dashboard. I have a visual which is grouping all payments due into an ageing bucket. I have the following:

image
The only issue is that ONE applicantid could be multiple payments in arrears. So the above picture is including a count for all individual payments per case. Opposed to only one count for the oldest payment due.

For example:
image
Would only show 1 count for 180+, since this is the oldest - 22/11/2023 (edit: 22/11/2024 and 22/12/2024 should be 2023!!)

Overall - How would i only include one count per case, which would be based on the oldest InvoiceDate. So for the above i would only see 1 count for CASE0001 which would in the ageing bucket 180+.

image

image

2

Hi @HarveyB-B
Is it possible to share a sample data and expected output?

Regards
Vetri

3

Hi Harvey @HarveyB-B,

You should be identifying the oldest record (one with highest days in arrears) for each applicant ahead of doing the bucketing.

If you create a sample on Arena showing your problem state, community experts can quickly create a copy from it, solve it and respond with a working solution.

If we don’t get your inputs within next 3 days, we will have to archive this post.

Regards,
Arun Santhosh
Pr Quick Sight SA

4

Hi there, sorry for late response have been off work. I ended up getting the solution.

I worked out all the dates the the consumer went into arrears by capturing the date when the case had an arrearsamount higher than 0. This was named intoarrears. so this was payment by payment based on each case. So to get it by only case, i got the min date of this new field by case:

image

So now MinArrearsDate would return the first date a case went into arrears. And then i did a date dif between this field and now()

image

5

Hi @HarveyB-B ,

Glad to know that you got it working. :slight_smile:

Regards,
Arun Santhosh
Pr Quick Sight SA