Date filter
Comments
-
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.
**If this answer solved your problem be sure to like it and accept it as a solution!
1 -
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
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 -
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 1 A 3/1/2020 2 A 3 A 3/3/2020 4 B 5 B 3/5/2020 6 C 3/5/2020 7 C 8 C When fitler for March 2020, I should get group Active user % A 67% B 50% C 33% 1 -
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?
**If this answer solved your problem be sure to like it and accept it as a solution!
1 -
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`)
----------
Serving up data insights since 2002...1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive