Access dyanmic date filter in Beast mode

user01546
user01546 Member
in Magic ETL

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

  • ST_Superman
    ST_Superman Domo Employee

    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
    ST_Superman Domo Employee

    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

     

     

  • DataMaven
    DataMaven Coach

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

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DataMaven
    DataMaven Coach

    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.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • ST_Superman
    ST_Superman Domo Employee

    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
    DataMaven Coach

    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.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • ostrichdude
    ostrichdude Member

    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.

    image.png


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

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

    Limitations:

    1. 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.
    2. 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.

Categories