Count of x field, based on y field as a calculated field

Question & Answer
,
1

I want to create a calculated field which is the count of payments down by case. What i am using at the moment is a table which just shows the applicantid, and the count of invoicedate. I have applied filters to show only cases in arrears for this table. What i now want to do is create a visual which is displaying the number of cases which are 1 payment down, 2 payments down, 3 payments down etc.

So the only way i can think of doing this is creating a calculated field which is the count of invoicedate based on each applicantid. The only issue is that i do not know how to approach this.

I have tried a distinct_count of invoicedate by applicantid which did not work. And then i tried an ifelse statement assigning a 1 to where invoicedate isnotnull by each applicantid. Again, i couldn’t get that to work.

Does anyone have any suggestions on how i can tackle this?

2

Hello @HarveyB-B !

When you say “1 payment down, 2 payments down, 3 payments down etc”, do you mean descending order of rows by payment date?

If so you could try using lag:

3

Hi @HarveyB-B. Checking in. We have not heard back from you regarding your question. We’d still like to help. If we do not hear back in the next 3 days, we will archive the question.

1 Like