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.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!