-
Convert all dates to last day of year
I am trying to convert all my dates to the last day of the year in a beast mode. I have a column in my dataset that is already showing the year for the data and have tried this STR_TO_DATE( CONCAT(`YEAR`,'-',12,'-',31),' %Y-%m-%d ') It doesn't seem to work. Any help would be appreciated
-
Can't Graph by Month
I've merged two tables and used the following formula to make sure the date doesn't get converted to String The two tables joined successfully. But My chart doesn't let me Graph By - Month The Table format still shows as 'String' under the Date columns. What can I do maintain the dates as 'Date' format so that I can Group…
-
Beastmode for Datetime to Group by Month in a Mega Table
Hi. I need to create a Logins by Month Mega Table. I have a datetime field for each login. I want to create a beastmode for the month of the login so that I can count by month. I want this column to sort correctly. I am having some trouble finding a good way to write the sortable beastmode for Login Month. Any ideas?
-
How do I convert a string to a date within Magic ETL?
I've seen questions asked about converting string to date, and answers were all given to use Beast Mode or add a separate field. Is there a way to do this in Magic ETL? I want to just have all the cleaning done in one place but can't find a way to do this in either version of Magic ETL.
-
How to properly partition in Workbench while allowing older data to come in?
I have a SQL Server dataset of 50M rows. Workbench runs the APPEND Update Method HOURLY. I use the table ID as the UPSERT key and the processing query checks the past 7 days ("Insertion Date") for new records. This updates older data unnecessarily with the same data, but protects against issues with the job not running for…
-
Help with Converting Time Zone - 'DateTimeOffset' to 'DateTime' in SQL Connector
Hey everyone, I'm having trouble with a conversion from DateTimeOffset to Datetime in a SQL Query (inside a SQL connector). I am trying to convert my date columns when they are imported in DOMO as UTC so they are coming in as the right time (Db is UTC, Domo is Pacific) and convert them to DateTime. When I run…
-
Social Media: Follower Total for yesterday
Hi guys, I am trying to create a formula that Account Engagement Rate. As a part of the calculation I need to work out what the most recent follower count is for the account. This would naturally be for the previous day as this will be the latest update. Does anyone know how to achieve this? Is it something like: Date…
-
Subtract Dates
I'm trying to exclude old items from my card. I'm using the following as a simple filter but it comes back as invalid. What am I doing wrong? Yes, I've confirmed they are both date fields. (CASE when DATEDIFF(`Close Date`,`POV End Date`) > 365 THEN 'Do Not Count' Else 'Count')
-
Period Over Period Multi-Value cards zeroing out when user changes the date
Good afternoon, I have several Multi-Value cards displaying both month-over-month and year-over-year values. They work great as is - but the values change to zero when a user changes the date at the page level. Is this supposed to happen or a side-effect of shoddy card building? Thanks!
-
Parsing Dates with Different Formats
Does anyone have any guidance for using a formula/function in a beast mode or using the regex functionality to ensure Domo recognizes all of the dates below as the same? 5/12/2022 5/12/22 2022-05-12 5.12.2022 5.12.22
-
Is there a way to create a static temp table in Beast Modes?
I'm the MajorDomo for a large retailer, and our Fiscal Calendar uses a 4-4-5 format. This means we have a Week 53 this year, and a large portion of our Domo reports are built off of a TY/LY comparison. I'm working on logic to reconfigure our reports always look 364 days or 52 weeks ago, rather than look at the same Fiscal…
-
Beast Mode Formula for Previous Complete Weeks
I need to display results in cards from the last complete weeks in a dataset. (This dataset is on a 7-day lag. Thus making it not compatible with the Period over Period cards. Which is cray cray to me. There should be an option to align to the data set rather than today's actual date... I mean... lots to love about Domo.…
-
How to Deal with Multiple Date Fields
I have a perplexing issue I'm trying to solve. I have data coming from several different data sources (all parts of Zendesk), each of which each represent a different kind of user interaction. For example: Email, Chat, Phone Calls These are all related to a master "Tickets" table, so they can be joined based on a Ticket…
-
Filter multiple months by specific days of the month
Hi - I'm looking for a way to take a card with a date range of the previous 13 months and have the ability to filter it for certain days of the month so I've got more of an apples-to-apples comparison. Ex., if today is July 15th then I want to filter the exact days to include so that other months on the card only show data…
-
Add Data that has been modified in the last 7 days
I have a snowflake dataset that has a modified date column. I want to merge the data that has been modified in the last 7 days to the dataset. Sometimes an order is modified after its placed and we want to capture the new values of the order. I have used this query but its not working SELECT * FROM "TABLE" WHERE…
-
How to convert Milliseconds into Date
I have a data set with timestamp represented in milliseconds and I want to convert it into date . Data sample: "1656076776827" Expected after conversion: "24 June 2022 09:19:36" I looked at the beastmode and could not find anything that would help me to convert it, any suggestions?
-
Pendo connection: how to add a date column to a dataset?
Hello! I have created a Pendo connection to domo but a date column was not pulled over with the data. I was wanting to add a column to the dataset and put the date that the corresponds with the data but am struggling to do so. Does anyone have any ideas on how to do this?
-
Limit Date Selector Card for User Interaction
I have a dataset that has multiple report dates with each value summing up total for that day (each row has its own value to sum for the day). I have a date selector card as a calendar format but need to limit the user to only choosing one day at a time since choosing more than one will make the data inflated and not…
-
Beast Mode Date issue
I'm trying to build a beast mode field that does the following... When the date_created is great than 131 and the last payment date is between 55 and 86 days, then we are in month 3
-
Generate Date Range Column from Two Date Columns
Hi all ! Im trying to generate a date column with a range of dates from two columns: Table I have (date format (mm/dd/yyyy) ) Id start_date end_date field1 1 01/01/2019 07/01/2019 text1 1 07/01/2019 12/01/2019 text2 2 01/01/2019 02/01/2019 text3 The idea is to generate a list of dates between start and end date with an…
-
Scheduled reports are aggregated even if the card shows data on a daily basis?
Good afternoon, Is there a way to have scheduled reports to display data on a daily level? Cards that show monthly data at a daily granularity level can be exported to a .csv/.xlsx at that same level of granularity; however, when distributed via Scheduled Reports, the data comes through at a monthly aggregate level. Can…
-
Dynamic Target Based on Current Date
Hello all, I am currently utilizing a gauge card with a target metric. What I would like to do is have the target be dynamic based on a date. For example, we have a different target for each quarter in our fiscal calendar. Depending on which quarter we are currently in, I would like the target to reflect the correct data.…
-
Forecast monthly expense
I'm trying to create a formula within ETL that will help budget owners understand how their expenses will be accrued over time. Ex: There's one Purchase Order that has $10,000 left to invoice, and the PO end date is July 31,2022. Our accounting team would expense them evenly for $2,500 for April - July. So I'd need to…
-
Is there a one-letter abbreviation for month?
I am aware that I can use %b for the abbreviated month (three letters) and %M for the full month, but is there a way to have a one-letter abbreviation for month? For example, "J" for January and "F" for February.
-
Adding business days to a date via MagicETL
Hi, I'm currently calculating a deadline using a dynamic formula in MagicETL. It looks like this: CASE WHEN `issue` = 'bug' THEN ( CASE WHEN `priority` = 1 THEN ADDDATE(`CreationDate`, INTERVAL 4 DAY) END ) The calculation works great, but I was wondering what would be the best way to update it to 4 business days, rather…
-
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…
-
Does anyone know how to convert Google Analytics "Date Hour and Minute"?
Does anyone have a solution they are using in a Magic ETL to easily convert Google Analytic's Date Hour and Minute output into something resembling an actual date time? ?
-
Issue with dates in ETL
Hello everyone! So we use filters similar to this in Domo and noticed that for some reason all results are being returned. What is happening below is we apply a filter on the rule to only look at 'Y' results that should be only applications hired this year, but instead we receive everything. We are working through it on…