How do I filter on two dates?
Long time lurker, first time poster here.
I need to create a metric within either the ETL stage or directly in the dashboard (I suspect it will need to be don at dashboard level) for meetings attended. It is calculated as:
Count of Marked as done date within date range AND Due date within data range
So for example if the dashboard is currently filtered to look at the previous month the metric will be: Count of Marked as done date between 1st Feb 22 and 28th Feb 22 AND Due date between 1st Feb 22 and 28th Feb 22
The marked as done date and due date wont always be the same day as the due date could be 28th Feb and the done date could be 1st March, in which case it wouldn't be counted in the metric.
Is there a way within the dashboard/cards to take two dates into consideration and if not is there a work around?
Also, for the record, I do not agree with how we are calculating this in the first place, but lets gloss over that.
Best Answers
-
Thank you for your response.
That would work if the dashboard was only ever looking at previous month, but they have the ability to change the date range, to anything
0 -
@TheBardsGirl If users can change the month then I don't think an ETL would work cause it would not be fully dynamic. You could try to setup a beast mode that looks at min and max dates so it takes in whatever range they choose.
0
Answers
-
It looks as though you are wanting to count when something is done and due in the same month. In which case, I would do this:
CASE WHEN Month(duedate) = Month(donedate) AND YEAR(duedate) = YEAR(donedate) THEN 1 ELSE 0 END
You can then sum this beast mode to get the count.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Thank you for your response.
That would work if the dashboard was only ever looking at previous month, but they have the ability to change the date range, to anything
0 -
@TheBardsGirl If users can change the month then I don't think an ETL would work cause it would not be fully dynamic. You could try to setup a beast mode that looks at min and max dates so it takes in whatever range they choose.
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 468 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive