Date filter

I want to set up a date filter, however, my current dataset only has a date variable "occurred date" which means only when there is an activity, it will have a date.  

 

Is there a way I can set up a date filter with generic calendar date?

 

thank you.  

 

Comments

  • Ashleigh
    Ashleigh Florida 🟢

    How exactly would you want to use a filter on a calendar date? How would a calendar date relate to your data? There is a also a Beast Mode function to use the current date.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    In the appstore, you can add the Domo Dimensions Connector. It has a dataset of dates that you can then use to join with another dataset so that you have all dates in a range of years that you need. You can find it in the appstore by searching for calendar or Domo Dimensions

  • This is simplified dataset, I would like to get the active user % in certain time period -  people can choose time range.   However, because I used Occured at to create a date fitler, whenever a date range  is selected, it always show 100% active user%.    Thank you.  

     

    USERIDuser groupOccurred date
    1A3/1/2020
    2A 
    3A3/3/2020
    4B 
    5B3/5/2020
    6C3/5/2020
    7C 
    8C 

     

     

    When fitler for March 2020, I should get 
    group Active user %
    A67%
    B50%
    C33%
  • Ashleigh
    Ashleigh Florida 🟢

    I see, how are you calculating your %? The problem is your other values are null so they are falling out, you would have to somehow fill in a value for them in order for them to get counted. How do you know those values actually included in March and not a different month?

  • As Ashleigh already noted, the biggest problem is that you have null values in `Occurred date`. Along those same lines, the larger dataset issue is that you are using a single column for double-duty, identifying the date as well as the activity simultaneously.

     

    For example, in USERID = 2, there is no way to tell that the inactivity occurred in March without a datetime stamp associated with it. You need to restructure your dataset to create a denominator to divide by.

     

    The most comprehensive solution would be to separate those two datapoints, and I would go with MarkSnodgrass's earlier suggestion and align your data to the Domo Calendar dataset. The SQL transform below should give you a starting point on how to recreate this dataset to get the calculations you want. I'm writing this code freehand, so forgive me if there are minior syntax or spelling errors. ?

     

    ----------

    Table Transform 1: tmp_User

    /* Create a unique list of all users */
    SELECT DISTINCT `USERID`, `user group`
    FROM tbl_UserActivity

    ----------

    Table Transform 2: tmp_UserByDay

    /* Create date record for every unique user */
    SELECT
    Cal.`dt`,
    User.`USERID`,
    User.`user group`
    FROM tmp_User AS User
    LEFT OUTER JOIN `raw_etumos_domo_calendar` AS Cal
    ON Cal.`dt` >= '2020.01.01' -- Set this to how far in the past you want to report
    AND `dt` < '2020.03.01' -- Set this to how far in the future you want to report

    ----------

    Table Transform 3: tmp_UserActivityByDay

    /* Populate activity records for every unique user */
    SELECT
    User.`dt`,
    User.`USERID`,
    User.`user group`,
    (CASE WHEN Activity.`USERID` IS NOT NULL THEN 1 ELSE 0 END) AS Activity
    FROM tmp_UserByDay AS User
    LEFT OUTER JOIN tbl_UserActivity AS Activity
    ON User.`USERID` = Activity.`USERID`
    AND User.`dt` = Activity.`Occurred date`

    ----------

     

    Output the above to a new dataset and you should be able to use a Beastmode calculation (example below) in your cards to get the percentages you are looking for. Let me know how it works for you.

     

    Activity Rate = SUM(`Activity`) / COUNT(`USERID`)

This discussion has been closed.