Help with creating just one single column containing different time frames
Hi,
I want to create a column that states the time period. I essentially have a dataset that consists of data that is divided across quarter, month and year. For example I have data that displays revenue for the current month, revenue for the current year and revenue for the current day. I want to create column that uses the date/time frame or something down these lines such that I could display the time period.
For example, Revenue for the day of 8th Nov 2023 would be time period day
Revenue for the October,2021 would be month and so on.
I am not sure how to do all this in one column.
I am a bit confused in this matter and was hoping I could get some guidance
Best Answer
-
This sounds like a use case for Variables, I would create a variable to select the time period: Day, Month, Quarter, Year, etc. and then a second variable asking for a date in the period.
This way if the date entered is 2/17/2024 then depending on the first variable (time period) you'd get:
- Day: only the entries for 2/17/2024
- Month: entries from 2/1/2024 to 2/29/2024
- Quarter: entries from 1/1/2024 to 3/31/2024 (or whatever your fiscal calendar might be)
- etc.
This will be done by creating a Beast Mode that will be used as a filter in your card (the date range should be set to show All Time), something like:
CASE WHEN `time period` = 'Day' AND `dateCol` = `reference` THEN 'true'
WHEN `time period` = 'Month' AND DATE_FORMAT(`dateCol`,'%Y%m') = DATE_FORMAT(`reference`,'%Y%m') THEN 'true'
…
ELSE 'false'
ENDYou'll need to build a case statement for each possible time period you create, but something like this should work.
0
Answers
-
Can you give an example of what you are wanting your dataset and new column to look like?
0 -
This sounds like a use case for Variables, I would create a variable to select the time period: Day, Month, Quarter, Year, etc. and then a second variable asking for a date in the period.
This way if the date entered is 2/17/2024 then depending on the first variable (time period) you'd get:
- Day: only the entries for 2/17/2024
- Month: entries from 2/1/2024 to 2/29/2024
- Quarter: entries from 1/1/2024 to 3/31/2024 (or whatever your fiscal calendar might be)
- etc.
This will be done by creating a Beast Mode that will be used as a filter in your card (the date range should be set to show All Time), something like:
CASE WHEN `time period` = 'Day' AND `dateCol` = `reference` THEN 'true'
WHEN `time period` = 'Month' AND DATE_FORMAT(`dateCol`,'%Y%m') = DATE_FORMAT(`reference`,'%Y%m') THEN 'true'
…
ELSE 'false'
ENDYou'll need to build a case statement for each possible time period you create, but something like this should work.
0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 557 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 571 Beast Mode
- 11 App Studio
- 28 Variables
- 579 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 352 Distribute
- 92 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 88 Community Announcements
- 4.8K Archive