-
Calculated Fields in Cards
I created a calculated field in beast mode (sum of 7 columns in my dataset). I need to use this field as a summary number in cards. I am calculating total brand impressions and need to show the total as a single number for each partner and to also show the total by partner year over year. When I add the calculated field to…
-
Receiving an error in my Beastmode | Multiple case statements
I am pretty sure it has something to do with having multiple case statements. I've the following code which gives me an syntax error: sum(CASE when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) then (case when `ConversionTypeName` like '%Sale%' then…
-
How to change timezone of a column in Magic ETL based on another column
How do I change timezone of a column in Magic ETL based on the input of another column. e.g. I have a column called Datetime, which has date-time values (e.g. 2020-12-07T00:00:00). I have another column called Timezone which has a timezone (e.g. 'UTC') I want to convert the datetime column from America/New_York Timezone to…
-
CASE statement if else Magic ETL
Hey guys I am trying to fill a column based on certain criteria from 4 columns. I think it would be best to do this in another column so that I'm not overwriting anything. My table looks like the following and I'm trying a Case Statement that fills in the null spaces, but I think my case statement is missing something or…
-
Total Column in a different column
Good Morning I have a column let's say (look below) Is there a way to create another column with the total sum of that column? So it reads a new column that says total and it should be the same number? Thanks! category category total A 1 A 1 6 A 0 A 0 6 A 2 A 2 6 A 3 A 3 6 A 0 A 0 6 B 0 B 0 2 B 2 B 2 2 TOTAL 8
-
Running Total with Magic ETL (not MySQL)
I've made this short video to explain what I'm trying to do:
-
Magic ETL Python script
Hello. I can't find any example to run a Magic ETL Python script. I have added a column. I want to populate it with a pythons script and it never works in the schema tab I have added a new text column named : "Alias" and here is my script : # Import the domomagic package into the script from domomagic import *# read data…
-
Pull Data From One Column Based On Condition From a Different Column
Hey guys! I've been at this for a while, Magic ETL, Analyzer, Google, nothing seems to answer the following (maybe it's because I'm new) I can't seem to find a means to pull data from a numerical column based on the condition of a categorical column without aggregating as a sum. Could anyone help me with this without…
-
Beast Mode for Top 20 then Other
I am creating a table to rank sales by each company for the last 3 years and totalling the revenue. I would like to show in a table the Top 20 companies by Total 3yr Revenue with the remaining companies aggregated in a row named "Other" rather than the individual company names. I was trying a beast mode to rank each…
-
Counting the occurrence of a certain character in string
Hello there, I was wondering if there is a way to count the occurrence of a certain character in a string. Specifically, I have a column that contains the gender information (M or F) of each data entry. All of the data entries are grouped according to a unique key, and while grouping, the gender column is collapsed with a…
-
DATE_SUB function
Trying to write a Beast Mode that calculates yesterday's date. DATE_SUB(CURRENT_DATE(), interval 1 Days) This formula validates just fine, but an error occurs when I try to use it in a table or see it in a drill down. Any ideas?
-
Case statement to filter by minimum and maximum values
I would like to be able to filter by a summed value. As Domo unfortunately does not allow Aggregated filters, could I bucket the locations into small medium and large by percentage of max and then allow a filter to be used that uses that case statement? Has anyone had a similar problem?
-
Combine 2 datasets with join or append while preventing duplicates
I have 2 datasets: * 1 containing conversions per day per product * 1 containing the costs per day (in total) I am trying to get to this: CostsSalesRevenueROITotal 1993600501Product A2200 Product B 1400 Total 2203600580Product A2200 Product B 1400 But I end up getting this CostsSalesRevenueROITotal 11983600402Product…
-
Rolling Count Calculation
Hi, I am trying to get a rolling count of data over a week, 4 weeks, and 8 weeks period. This is the sql query I have in the transforms dataflow. Then in the analyzer, I would select Count of 'Rolling_Week', 'Rolling_4_Weeks', and 'Rolling_8_Weeks'. But I am getting 0 for everything, which is not accurate. Could anyone…
-
Summing Up Last 12 Weeks Beast Mode
Hi There, I am trying to do write a beast mode to sum up Revenue from the Last 12 weeks but not counting the previous week. for example Last Week = 11/22 - 11/28 (Sunday - Saturday) I want to go back 12 weeks from last week, so I would start from the Week of 11/15 This is what I've come up with but it doesn't seem to…
-
Recursive dataflow Issue - No Data
Aloha All: I have a recursive dataflow that is wiping out my start and end dates when the new data is uploaded. Current data has the start and end dates; however, the historical data is missing the dates. The dates were originally there in the initial upload. My recursive has a join the new data and historical data. Then I…
-
Best Mode: Cumulative Sum
Hi All, I am trying to create in Best Mode a Cumulative Sum of column `Amount` to be represented in a table format. I want to take the `Amount` 50 and count it for all months from `Start Date` to `End Date`. In the example below, I would like to count 50 for July, August, September, October and November. Do you know if…
-
Is it possible to create or update beast modes using an API?
Hello, I work with multiple datasets (8+) that frequently need their beast mode calculations adjusted to stay up to date. The changes are small, but copy and pasting it to multiple tables is repetitive. Is there an API available that can be used to do this? Maybe there is an available work around too. Thank you!
-
Joining based on conditions in Magic ETL
Can we make joins based on conditions in Magic ETL? e.g. I want to replicate the below in a join in magic ETL. Can I do it? SELECT dataset_1.* dataset_2.* FROM dataset_1 LEFT JOIN `dataset_2` on (dataset_2.id` IS NULL AND `dataset_1`.`Day` >= COALESCE(dataset_2.date`,dataset_1.date`) )
-
Group provinces by East vs West
Hi there, I'm stuck with a beast mode formula and hoping the community can help me out with this formula. CASE 'PROVINCE_CD' WHEN 'BC' THEN 'WEST' WHEN 'AB' THEN 'WEST' ELSE 'EAST' END Thanks in advance!
-
How to delete rows automatically based on a date in a dataset ?
I am inserting new rows in a dataset on daily basis, and now the dataset size has became huge since the rows are added daily. I want to delete the rows from this dataset based on date criteria (Dataset has a column type of "date" in it). So the criteria is like I want to preserve rows in dataset for last 30 days. eg…
-
Filling empty cells in a column with the last value
Hi there, I am currently stuck in a problem to fill empty cells of a column with the last value that is stated in that column. So I would like that the the cells of the column 'Cost Center Basis' are filled with the last stated value e.g. 'Kostenstelle/-träger: 1000010Bezeichnung: O001 General' until the next value is…
-
Redshift -- Significant Slow Downs Recently? (November 2020)
Good day Domo folks, Is anyone else noticing a significant slow-down with their Redshift transformations? My average run-time has doubled and in some cases, tripled or more. This started on-or-around 11/16/2020. I reached out to support but was advised "Domo has not control as it is AWS/Amazon". The MagicETL feature is…
-
50 day rolling average in ETL? Is it possible.
I have seen solution to a 5 day rolling average with 5 lines of code to build to 5 days, gets big with a 50 day average Do not want create 50 extra columns to build the rolling average.
-
Combining data sets without duplication
I have 2 datasets: * Contains individual transactions with an employee's name Employee Name Sale PriceSale DateJohn Doe100 11/2/20John Doe250 11/6/20Jane Doe50011/8/20Jane Doe10011/12/20 * Contains each employee and their number of consultations and new clientsEmployee NameStart DateEnd DateConsultationsNew ClientsJohn…
-
help with logic - finding unique number of `column1` that have BOTH value '1' and '2' from `column2`
I am struggling through working out the logic for this. Can anyone help me think through this? There might be an entirely different approach that is much easier. I am sorting out an email campaign and I want to know how many unique `EmailAddress` show up for BOTH `EmailName` value 'email1' and 'email2' at least 1 time. (To…
-
Magic ETL - filling values in a column based on other values in the same column
Hi there, Ran into a bit of a roadblock with my MagicETL. I was able to get to a certain point using the Rank & Window function but I can't figure out how to do the next step I need. Below is an example of where I'm at after combining two tables and partitioning them based on ID and sorting them by date.…
-
Reverse Running Total by Date
Can I get some help in understanding how to take the reverse of cumulative totals based on date? I've attached a sample of the data, which has US State and County cumulative data by date and I would like to calculate total from one date vs the previous date and keep the grouping of state and county. Any help would be much…
-
Bucket Series for 100% Stacked Bar Chart
Hi, I have a 100% stacked bar chart for sales that has about 350 options in the series `Company Name`. The units are the number of sales so: count(`Sale Amount`) I would like to bucket the companies that make up 1% or less of the total amount to simplify the legend. There is not a "limit rows" option for this type of…
-
Domo Percent of a Ratio?
Hello All, I have a simple horizontal bar displaying a ratio by reporting categories. For example, Category A - 27.9% Category B - 26.8% Category C - 25% etc. I would like to add a series that colors those bars by a sub category, but without losing the above. Right now I seem to get the % totals of the sub categories…