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

Comments

  • 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?

  • 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.1.png

     

    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)

    2.png

     

     

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

  • 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.  

  • 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. 

  • 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.