Determine Pending at End of Month Based on Two Dates

Question & Answer
,
1

I have two dates referal_date and disposed_date. I need to count how many cases were pending (not disposed) at the end of each month that were referred in the previous month(s).

so if I had a data set like:
referral_date disposed_date
1/1/2022 null
2/5/2022 1/10/2023
1/31/2023 2/10/2023
12/31/2023 null
2/15/2023 3/2/2023
3/13/2023 3/31/2023

If I ran the report on March 15th for I would expect to see 3 pending cases for the end of Feb. The 1/1, 12/31, 2/15 cases were all pending at the end of Feb since the 2/15 case was disposed of in March.

Ideally what I would have is a Table or Pivot Table that shows a dimension of the left with the # referred by YYYYMM, #disposed by YYYYMMM and the #pending by YYYYMMM.

2

Hi,

May this help you.

regards,
Naveed Ali

3

Hi @ugadawg90
I wonder if you could use a calculated field something like
IsPending: ifelse(dateDiff(referral_date, now()) >= 1 AND dateDiff(disposed_date, now()) <= 1, 1, 0)
Then you would have a 1 or 0 depending on if it was disposed. I’m using now() in the calculation, but if you wanted to run the report with a user selected “as at” date, you could pass that instead.

4

Let me check that this week. I appreciated the reply.

5

I am testing this formula. I had to account for the Dispostion Date being null.

ifelse(dateDiff({Referral Date}, now()) >= 1 AND dateDiff({Disposition Date}, now()) <= 1 OR dateDiff({Referral Date}, now())>=1 AND isNull({Disposition Date}), 1, 0)

My dataset has some duplicate rows so chose Max for the aggregation and then put a table calculation of Running Total. I think I am almost there.

6

Hey @ugadawg90

Does this problem still exist?

Where is the data off from what you have now?