Create beast mode dates
Hi everyone, help needed! Here is how my table looks below. What I want to show in Analyzer is the count of accounts of these services over time. My problem is how the dates are shown here. Any advice on what I can do? I thought about creating a calculated field to index dates but not sure how to do it. Any help is appreciated!😀
Account Service Start Stop
1 mobile jan 26 aug 14th
4 tv apr 9 sep 20th
6 radio jan 9th aug 4th
Comments
-
The STR_TO_DATE function is going to be your friend in this case. Try this for your beast modes.
Start
STR_TO_DATE( CONCAT( REPLACE( REPLACE( REPLACE( REPLACE(`Start`,'th','') ,'st','') ,'nd','') ,'rd','') ,' ',YEAR(CURRENT_DATE()) ),'%b %d %Y')
Stop
STR_TO_DATE( CONCAT( REPLACE( REPLACE( REPLACE( REPLACE(`Stop`,'th','') ,'st','') ,'nd','') ,'rd','') ,' ',YEAR(CURRENT_DATE()) ),'%b %d %Y')
Here's the breakdown of what I'm doing from the inside out.
Use the replace function multiple times to remove the th, rd, st, nd from the day of the month.
Add the current year by using the year function and the current date function so you have a complete date.
Use the concat function to build a string that would look something like this: Apr 4 2022
Use the str_to_date function to convert that to a standard date. The %b %d %Y informs the function what format the string is currently in.
Hope that makes sense.
**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.2 -
I don’t think I explained well enough. I want to show in visualization dates on an x axis and a count of active services on the y. This would be a line chart. Unfortunately now there is just a start and stop date. How can I visualize this?
i think I need to find a way to show the months each account is active.
0 -
Put the start date beast mode I gave you in the x-axis and put either the service field or the account field in the y-axis and then choose the aggregation type of count if it doesn't automatically do it for you. That would give you a line chart by start date.
**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.0
Categories
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 182 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive