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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive