I am creating a dashboard for a report. This report is relating to collections and arrears. I have a KPI which is just how many cases we have in arrears. Alongside i have created a trendline by using free form mode in layout settings.
The KPI for arrears is a distinct count which just states how many distinct cases we have in arrears. However the trend line is using the multiple counts for each caseID. This is because if a payment is 3 months in arrears, it will show 3 data points in this trendline.
For example if i have 50 payments in arrears, but that is actually only 35 distinct cases, the trend line will be showing 15 more counts/datapoints. So although i have 35, if i add up each datapoint in the trendline it will = 50.
My main question is how can i create a calculated field which shows the distinct count of a case, but only factors in the date it was first seen?
Hello @HarveyB-B, we should be able to manage this in an ifelse statement. We can check if the date field is equal to the min date for that case, and only return the case ID when that is true. It would look something like this:
ifelse(minOver({Date}, [{Case ID}], PRE_AGG) = {Date}, {Case ID}, NULL)
Using that field instead of the Case ID directly should resolve the issue you are seeing. Let me know if that helps!
Hi there, this works as i asked perfectly, the only thing i forgot is that this is showing months cases weren’t in arrears.
What I now need to do is expand on it and have it based on the calculated field you provided, but where the field hasfullypaid = 0.
Would this be doable by including another ifelse statement which looks for a date the case was first seen where hasfullypaid = 0, and assign a 1 to it?
Solution: I created a field beforehand which filters the cases to what i need:
ifelse(HasFullyPaid = 0 AND NominalCode <> ‘49b814eb-36d5-4030-a25a-70b800c2624f’, InvoiceDate, null)
And then in the code you provided i called this new field, opposed to the caseID field
ifelse(
minOver(NEWFIELD, [applicantid], PRE_AGG) = NEWFIELD,
applicantid,
NULL
)
Hello @HarveyB-B, thank you for following up. I am glad you were able to get it to work!