Get Data Based on Comparison of 2 Dates
I have a Dataset with 2 dates open_date,closed_date for a category(has 2 values open,closed).
I want to build a weekly bar chart with a count of category where the date range is selected for the column open_date but the date range should also be applied to closed_date as well.
|Open Date||Closed Date||Category|
For the example above if i keep the bar chart as weekly and select the date between 2020-07-01 and 2020-07-07 it should give me a count 1 as the category open and closed between that date is 1.
Can somebody help with this
Step 1. make sure your measure is aggregatable .... so instead of 'Open' / 'Closed' use a 0 or a 1 so we can take the SUM() or AVG() .
For your reporting requirement, i think i would be VERY clear about the question I'm asking. are you asking:
- "at the start of the week, how many projects were open?" or are you asking
- "how many projects were closed during the week?"
They aren't the same thing, and by being clear in what you want, you can give your card a clear title and minimize confusion for the users.
Either way, I would structure the data so that I have exactly one row per week and isOpen = 0 or 1.
if you can have multiple categories, ex. cat 1, cat 2, cat 3, then you need one row per week per category and your metric.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 189 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 78 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 172 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive