A way to count records open (but not yet closed) per dynamic period

texas

Hello community,

I have data that exists as such:

RECORD ID, OPEN DATE, CLOSE DATE

12345, 01/01/2020, 02/15/2020

12346, 01/15/2020, null

12347, 02/01/2020, 3/31/2020

And I have the same data pivoted as well:

RECORD HISTORY ID, RECORD ID, STATUS, STATUS CHANGE DATE

98765, 12345, Opened, 01/01/2020

98766, 12345, Closed, 02/15/2020

I am trying to produce a report that would show the records that were open (had opened, but not yet closed), grouped by period, hopefully where the period remains dynamic so my report viewer could group by month, quarter, year on the fly as they wish.

Let's say my report consumer wants to see total records that were open per month. A record opened in January but closed in mid-March would count in each of January, Feb and March's numbers. I assume this means that I have to explode my data in some way with an ETL process (I prefer to use a DataFlow) but I cannot currently wrap my brain around how to accomplish this. I'm feeling a bit dense and would love any pointers or guidance.

Thank you very much in advance.

Find more posts tagged with

Accepted answers

GrantSmith

Hi @texas ,

You could utilize the Date Dimension dataset provided by Domo and utilize a join to explode your dataset to populate for each day. Something like (this is untested back of the napkin):

select *
from `dates` d
join `my_table` t on d.`date` BETWEEN t.open_date and t.close_date

This will list each record ID for each date where it's within the open and close dates so you might need to be careful of duplicates in your counts / sums.

All comments

GrantSmith

Hi @texas ,

You could utilize the Date Dimension dataset provided by Domo and utilize a join to explode your dataset to populate for each day. Something like (this is untested back of the napkin):

select *
from `dates` d
join `my_table` t on d.`date` BETWEEN t.open_date and t.close_date

This will list each record ID for each date where it's within the open and close dates so you might need to be careful of duplicates in your counts / sums.

jaeW_at_Onyx

@texas , @GrantSmith nailed the answer on the head.

Only thing you'd want to do is fill cases where the START or END date are NULL. (usually for EndDate I'll set it equal to today).

If you don't, you'll eliminate all open projects.