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?
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.
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.
|USERID||user group||Occurred date|
|When fitler for March 2020, I should get|
|group||Active user %|
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`
Table Transform 2: tmp_UserByDay
/* Create date record for every unique user */
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 */
(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`)