Active Plans by Month

Jean_McDonald

I need some help trying to figure out an issue. We are wanting to know how many active contracts we have in our system, during any given time. Here is where I am running into the problem. If we use say our upload date, we would not be including any contract from prior to the upload date. Same thing would happen if used Effective Date of when the contract would become active. Our contracts can be active anywhere from 1 year to 10 years depending on what was purchased, and we only want to know the Active count, so if it expires, it should fall off. For example. Today is 9/27/2023, but we want to know how many Active contracts we had in our system during the month of April 2022. The dates we have in the system that we use are as follows: Plan Purchase Date, Product Purchase Date, Transaction date, Effective Date, and Expiration Date.

I currently have to where I can see all Active as of Today that are in the system, but I am not able to split out by month. So like we have 2,389,333 as of Today that are in Active status. Side note, We enter contracts into our system Daily.

Below is what I tried to set up, based off the Effective date, but its not what is being asked for.

marcel_luthi

This would all depend on what you need to show and there are 2 different ways this can be approached.

Using a variable This approach works when you want your users to enter a single date and then you just return the count by creating a beast mode that filter the contracts that are active on that date by comparing the variable value with the Effective Date, and Expiration Date, so if the variable date is between the 2 you show it, otherwise you remove it. This has the ability to provide a daily view, but it'll only show 1 point in time at a time, so not useful if you want a time series.

Using an ETL and the DOMO Calendar Dataset This means you'll create a new dataset that will have entries for whatever granularity you want to show, lets say you care about having information by the end of each month, you'll filter the calendar dataset only to the EOM dates, and the do a Cross join of this and your current dataset to then filter to only keep the entries that are active at EOM. This was you can have a monthly view on how it has changed and how deals will fall out in the future.

GrantSmith

@Jean_McDonald

Yes, the Domo Dimensions Calendar Dataset has dates through 2030 in it so you can view future dates.

marcel_luthi

This would all depend on what you need to show and there are 2 different ways this can be approached.

Using a variable This approach works when you want your users to enter a single date and then you just return the count by creating a beast mode that filter the contracts that are active on that date by comparing the variable value with the Effective Date, and Expiration Date, so if the variable date is between the 2 you show it, otherwise you remove it. This has the ability to provide a daily view, but it'll only show 1 point in time at a time, so not useful if you want a time series.

Using an ETL and the DOMO Calendar Dataset This means you'll create a new dataset that will have entries for whatever granularity you want to show, lets say you care about having information by the end of each month, you'll filter the calendar dataset only to the EOM dates, and the do a Cross join of this and your current dataset to then filter to only keep the entries that are active at EOM. This was you can have a monthly view on how it has changed and how deals will fall out in the future.

Jean_McDonald

Marcel_Luthi would this also work for future dates? The ETL and Calendar data set? They are looking to see the number of active contracts so they can create a forecast the possible number of phone calls for service claims that would come into our center.

GrantSmith

@Jean_McDonald

Yes, the Domo Dimensions Calendar Dataset has dates through 2030 in it so you can view future dates.

Kristefor

To simplify this problem - are you saying that on a <given date> the number of active plans would be:

*those plans that have an effective date on or prior to the <given date>

*have an Expiration Date (or perhaps null) after the <given date>

So then, you apply that logic along with a join to the Domo Dimensions Calendar Dataset get your results?