-
Dynamic Annualized Number- Using Hard Coded Number Now for the Month
I have a beast mode that is currently using a hard coded number for the month (i.e 7 for July) and I want to make it dynamic to update each pervious month we close. Below is my current beast mode, which works but is not dynamic: (SUM(case when `Period`>='2019-01-01' and `Period`<'2020-01-01' and `Ledger Type`='AA' and…
-
Exporting Unique Values of Column
I'm trying to export a list of the unique values in a given column. For example, I have a dataset of pipeline data with ~2million rows, and I just want a list of the opportunity owners in that dataset. Is there a way to export that list without exporting a 2M rows and pivoting for owner name? Thanks!
-
Partition By Beast Mode as a Filter
I created this Max('Value') that is grouped by a certain NamingKey using the Partition By. I followed the syntax recommended by DOMO in the attached image. I was able to get Max and Min values and use them within a Case statement, but I could not filter from that Case Statement. The Partition By Beast Modes just don't…
-
Add Rows Blend not working
I'm trying to combine two datasets together. Given the size of one of the datasets (62MM rows), I went with the Add Rows Blend. I made sure to match my columns in name and type (as shown in screenshot), but when it came time to put them together, I'm not seeing the option to run the Blend. What am I doing wrong? Thanks so…
-
Beast Mode If Statement
Looking of help with a Beast Mode. What is the Beast Mode for including in the "count" only "Yes" answers included on a domo synced Google Sheet. I tried this: case when `Offer Made` is not null and 'Offer Made` = 'Yes' then `1` else 0 end But it was "invalid" I have a vertical group bar chart that show the count of…
-
Issues with Converting Text to Date
In the Dataflow, I actually concatenated the MONTH('Date Column') and Year('Date Column') but when I am trying to convert that to date using either DATE() or STR_TO_DATE() functions, I am getting no values in the column. The deired output is like "Month name - Year" which should be of a date data type. Any help is…
-
How do I replace text with unicode
Hello, I'm trying to replace some characters in my table using Unicode Characters such as :white_heavy_check_mark: and with :cross_mark: This is the code I typed into the Beast Mode but it's not displaying it and instead only showed the literal '✅', could anyone please help? Case when `Last Week Actual` - `Last Week…
-
Simple JSON parsing
I have a field in a table that contains values stored in a JSON string like this: {"enabled":true,"timeout":220,"timeout_buffer":1} There are other fields in the table as well but only one of them contains JSON values. I would like to parse out the values into appropriately named columns, ie. create new columns called…
-
MYSQL Transform: Replacing date on historical input based on update of second input
I'm looking for the best way to do this. I have a SQL Transform with a recursive input and a second input which I'd like to have replace the input of the recursive data based on the date in one of the columns of the second input. The pictures below should help because I'm just as confused explaining this as you probably…
-
Adding totals based on titles
hi, I am trying to group together user totals based on their adjacent column title I have tried groups and cases but cant get this to work. My latest attempt is below, can anyone help. SUM( CASE when `PS Variable Stub`="ADenabled" or `PS Variable Stub`="O365enabled" then `GUID_ User Count` else 0 End ) Thanks
-
Keeping historical track of live data
Hello, I'm working with data from our CRM. To simplify things, let's say we only have one Pipeline, or sales vertical. A very simplified version of what I'll have in my data set is two columns: Deal Title / Deal Value. Deal A / $100 The data from our CRM is updated constantly and so the data points are always changing.…
-
add leading zero
Is it possible to add a leading zero to a field if it has a specific number of digits....problem I'm having is I have a field "routing number" which for the records that have 9 digits it displays all the numbers but the ones which have 8 digits it removes the leading zero. thanks
-
Calculating 2 Previous Months
Hi Guys, Trying to sum previous month value and the month prior value to display in a Comparative Gauge. So, this month being September, I require August total expenses and July's total expenses. Appriate your help.
-
Add Column Error in MySQL Transform
Hi, I'm trying to map Google's LocationCriterionServices IDs to some Geo data from Adwords. I keep getting an error in my (case when) statement, but the SQL resources I've been checking haven't shown me what I'm doing wrong. Below is my code. Is there another way to map 1000+ values easily in MySQL that I'm missing? alter…
-
First day of the week
Hello. I have an excel output which has data fed everyday. that contains all information about how many sales we made everyday. I would like to put on a dashboard that gives ne weekley information. So how can I code in magic ETL/ Beat Mode. So I can make my first day of the week as Sunday. eg: August 25th 2019 to August…
-
Getting beast mode to calculate 10% value of a column
Hello Domo masters, Help Needed. I am trying to build a card that would display projects in a table when the following criteria is met: 1. Approaching budget (Actuals >90% of Budget) 2. Over Budget (Actuals > Budget) I tried the following calculation in testing: Case When `Project Actuals YTD` >= ((`New Budget`+`Rollover…
-
Card and Beast Mode to Isolate Best Performing Stores
Hi all, I need some ideas and help in trying to isolate best and worst performing stores based on say a monthly or YTD YoY ratio. I don't know yet where to start but I would like to have an option to look at YoY differences for the month or for the year and then develop a ranking type chart of best and worst performing…
-
Newly created dataflow getting failed with error message "Unable to update to Dataset"
Hi Team, I am facing one issue from yesterday i.e., Dataflow getting failed with error message "Unable to update to Dataset". I have created this dataflow yesterday and try to run it many times with different logic but it is getting failed every time with error at "Output Datasets" step. Even, I have created two more…
-
User retention Beast Mode Help
Hello, I'm looking at my dataset on our platform users and am trying to make a domo card that shows the "quick ratio" for each month, which is (New + Reactivated) / Churn. My dataset has the following columns: Month / Active Users / New Users / Reactivated Users For example for Month 2, Churn is found by: Month 1 Active…
-
Combining values in 3 columns into 1 new column
I have 3 columns of dates (for example purchase date of: Column 1 - Apple , Column 2 - Pears, Column 3 - oranges). I want to create a new column that combines all 3 of these column dates to display a single column of dates. This is to be used for another set of data analysis (where the date is important and needs to be…
-
Rounding a date/time field to the nearest half hour
We have a date/time field that I'd like to accomplish the following with: * Split the date and time into two separate columns (while also maintaining the original column) * Round the time down to the nearest half hour increment (for instance, 8:43 would round to 8:30, 8:18 would round to 8:00, etc.) Issues to be…
-
YOY in table card beast mode
How can i display YOY Sales Data side by side in table card using beast mode. I am using this beast mode to display LY Case when year(DATE_SUB(CURDATE(), interval 1 year)) = year(`Date`) then `Net Sales` end and date selected in table card is This Month
-
Excluding weekends (using DATEDIFF function to identify employees first 3 days as training)
A search for Exclude Weekends and Exclude Weekends using DateDiff Function returned a few results, none of which would seem to work for what we're trying to do so checking to see if we can get some help on this! We're trying to add, in the dataflow itself, as part of the output dataset, a transform that would add a new…
-
Beast Mode for Count Distinct with Condition
I want to get count of values in a column based on a condition for another column: Example: Count(Distinct 'visitor_id' ) where visitor_called = 'Y' The above give me a syntax error. Trying with 'Case': (CASE WHEN `visitor_called` = 'Y' then COUNT(DISTINCT `visitor_id`) else '0' END ) This returns me overall counf for…
-
How to map 1 value into the same row of another column in ETL
Please see attachment. I need to map a value from one column to another. Context: Marketing's Department ID needs to be the same as its Organization ID. I've tried Value Mapper but it seems like that will only allow me to make a brand new column. I don't want a brand new column. Please help! Which ETL function should I use?
-
ETL Actions: Edit Columns: Set Column Type
As stated in the knowledge base (https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/02ETL_Actions%3A_Edit_Columns), the definition for the Data Type "Decimal (Fixed)" reads as follows: "Contains decimal numbers with a fixed number of digits after the decimal point". The definition does little to clarify the…
-
MoM formula in table card
Hi, I trying to create a table chart for keyword rankings. The chart will have 3 columns: Keyword, Rank(Last Month), Rank Change (MoM). I am having trouble showing this in a table chart format. If the table is only supposed to show last month's Position, it filters out other dates - so how would the Rank Change MoM be…
-
Python Node giving throwing up an error I don't understand
When I try to run my python script, it throws this error: Traceback (most recent call last): File "<stdin>", line 7, in <module> File "/home/domo/domomagic/io.py", line 17, in read_dataframe return pd.DataFrame(read_array_dict(input_name)) File "/home/domo/domomagic/io.py", line 26, in read_array_dict…
-
Issues making my calculated field work
case when ((SUM(CASE when `Activity Date`>=CURDATE()-28 then `Score` end))/(SUM(CASE when `Activity Date`>=CURDATE()-180 then `Score` end))*100)>=50 and (SUM(CASE when `Activity Date`>=CURDATE()-180 then `Score` end))/ 307>=0.5 then COUNT(`Account`)end what am i doing wrong to make this work. It is not coming up as an…
-
How can I get each x axis point to be a percentage of the total?
I'm having trouble getting units as a percentage of a total. I have a Y coaxis "Views" for example, and an X axis with Dates, and each date has a specific amount of views. I want each date to show as a percentage of the total views within the date range on my X axis. If anyone could help that would be great. Thanks!!