Access dyanmic date filter in Beast mode

I want to leverage the date filter parameter dynamically in beast mode. For example: I have 2 date fields that are populated when an account is opened (Date Opened) and when an account is closed (Date Closed). I would like to have a count of active accounts based on the date range that is dynamically selected on the card. Using the data below - I would expect the following results based on the date ranges selected: Active means the account was open on or before the end of the date range and not closed on or before the end of the date range

Date range: 12/31/2018 - 2/5/2019 = 3 active accounts (accounts 1, 2, 3)

Date range: 12/31/2018 - 4/1/2019 = 4 active accounts (accounts 1,3,4,5)

Sample Data:

Account Date Opened Date Closed

1 1/1/2019

2 1/5/2019 3/5/2019

3 2/2/2019 4/5/2019

4 2/14/2019

5 2/21/2019

6 4/8/2019

Find more posts tagged with

Comments

ST_Superman

What would you expect to see if the date range was:

1/4/2019 - 3/6/2019

Since Account 1 was opened prior to the date range, would you expect it to count because it was not closed before then end of the date range?

Since Account 2 was active for nearly the entire period, would you expect it to count for this date range?

ST_Superman

I decided to cross join your data with a calendar table to help me fill in each day an account was active.

You can find the Calendar data set by searching for Domo in the cloud app connectors. Domo Dimensions has a calendar data set you can use.

I then joined it in a MySQL dataflow with the following transforms:

SELECT
	a.*
    ,b.`dt` as `Date`
FROM
	`dojo_user01546` a 
    CROSS JOIN 
    `calendar` b 
WHERE a.`Date Opened`<=b.`dt` AND ifnull(a.`Date Closed`, DATE_ADD(CURDATE(), INTERVAL 30 DAY)) >= b.`dt`
ORDER BY a.`Account`,b.`dt`

SELECT
	a.*
    ,case when `Date Opened`<=`Date` and ifnull(a.`Date Closed`, DATE_ADD(CURDATE(), INTERVAL 30 DAY))>=`Date` THEN 'Active' else 'Closed' END as `Account Status`
FROM `transform_data_1` a

I chose to add 30 days to the current date as the "close" date of any account that was still open. I suppose you could change this to whatever your cancellation policy might state. If an account can close at any time then you may want to just leave it as curdate().

With this data set, you can create a card and mae a beastmode to calculate the number of active accounts for any given day:

COUNT(DISTINCT 
     case when `Account Status`='Active' then `Account` end)

DataMaven

Ignore this reply - Accidentally posted reply I had for another post - too many open at once!

DataMaven

Can you just use a case statement to count if the close date is either null or higher than the selected range? The filter would be on the dates already, so you could probably use max date opened as the criteria in the beast mode.

If the nulls are an issue, as they often are - you can address this in your ETL by setting null values to a date in the future.

One solution I have seen is setting the close date a week ahead of 'Current Date'. That way, it's dynamic and you can never catch it, but the field is always populated. Gets tricky if the field is visible to others, so you can do this in a new field, where all rows have values, but it's only used for criteria/calculation, and not visualizations.

To @ST_-Superman-_ 's point, it does get trickier when you want to count a location if it was active at all during the filtered period. Use of date functions like those I just posted in another discussion could be used, perhaps.

ST_Superman

The real issue here is that you have two date fields. You have to select one to build the card around, which is why I chose to cross join the calendar table so that all dates would be captured. You can then calculate the “active” number by comparing the open and close dates.

DataMaven

In my suggestion, you would be filtering on the first date column, and doing a count based on the second. The first would be a regular filter. The second would be what you would achieve in the beast mode.

ostrichdude

I know this is an old thread, but I just spent an ungodly amount of time looking for an answer to this question and didn't find anything that worked for me, so I wanted to share ere in case it helps other people.

Everything you need to understand is in this screenshot, but I'll explain below.

Here's what I ended up doing to have date filters that capture accounts that were active at any point between two dates (e.g. date_created and date_closed).

Create a Beast mode calc_calc_end_date, which auto-fills any blanks in date_closed with today's date.
Create two range selector cards.
Name one of them "Start Date" and define its min and max in terms of the calc_end_date column.
Name the other one "End Date" and define its min and max in terms of the date_created column.

Limitations:

If the date_created is later than the calc_end_date, this may produce unexpected output. As a result, this approach might not be appropriate for datasets where this is possible. Additionally, data quality validation checks may need to be put in place to prevent this from happening.
The UI is only moderately user friendly, since it can be challenging to get an exact date by using the slider. That said, the dates can be adjusted in the filters above, but this resets the appearance of the range selector filter cards.