Date filter

Reply
Highlighted
Major Blue Belt

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.  

 

Highlighted
Major Brown Belt

Re: Date filter

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.

Highlighted
Black Belt

Re: Date filter

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




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Tags (2)
Highlighted
Major Blue Belt

Re: Date filter

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%
Highlighted
Major Brown Belt

Re: Date filter

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?

Highlighted
Yellow Belt

Re: Date filter

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. Smiley Happy

 

----------

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


----------
Serving up data insights since 2002...
Tags (1)
Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.