Calculated field to divide a post filter field by a pre filter field

Q&A
, , ,
1

I have a simple calculation which is the % of cases in arrears.

I have CasesInArrears which is the count of cases where arrearsflag = 1.
I have applicantid which is a string field of all caseID’s. They are formatted as a string, however i am getting the count.

The calculation is CasesInArrears / distinct_count(applicantid)

However i have a filter on the dashboard which filters to different types of arrears cases. For example a defaulted case (where days in arrears is greater than 90). When i filter to defaulted cases, the % is always 100%. This is because its doing casesinarrears (10 for example) divided by distinct_count(applicantid) (which is filtered to default cases so is also 10).

How can i do:

CasesInArrears / distinct_count(applicantid)

BUT at all time the distinct_count should stay the same regardless of any filters applied?

So would be 10 / x amount of total cases

2

This was the solution to get it working

The greyed out areas are what defined a case as ‘arrears’

image