-
Creating buckets for unique ids to fall into, based on the sum of donations of those unique ids
Capturing donor frequency: We want to count the number of donations a donor has given per procedure. The amount of donations is the bucket I want donors to fall into. So, if we had 100 donors give 4 donations, we want those donors to fall into the bucket '4', and then we would count the unique donor ids. The part I am…
-
Conditional LIKE Statements, Does not Contain, NOT LIKE: Beastmode Only
Hey guys, To organize my blox_section column I created a beastmode formula under the Data tab in the Analyzer tool to create a new column called "Content Topic": My Case Statement is the following: (Case WHEN `blox_section` LIKE '%crime%' THEN 'CRIME & COURTS' WHEN `blox_section` LIKE '%sports/high-school%' THEN 'HIGH…
-
Beast Mode Help
Help, my brain is stuck. I am trying to come up with a BM that will filter my data. I have an orders dataset and for some reason someone chose Wednesday as their reporting day. I want to show that if the day is Wednesday then we will show orders from the current Wednesday to the past Wednesday. But if we have not reached…
-
Beast Mode
Hello Dojo! Does anyone know what I am doing wrong here. Thank you!!! case when `Union_Class` = 'A1' then case when `Class` IN ('A1','A2','A3','A4','A5','A6','EO','TB','TA','J','SF','F','SGF','GF') then 'Normal' else 'Error' end else case when `Union_Class` = 'A2' then case when`Class` IN…
-
Can you find a Beast mode by it's function?
Hi We have a primary data feed and that datafeed contained a 15 digit ID. We recently updated the datafeed to the updated 18 character Account ID and in Domo we used to perform a String Operation that performs a left trim from 18 to 15 characters prior to using a Join Data tile. We wanted to find all of the Beast Modes…
-
Table vs SQL Data Flows and which functions does Domo not recognize? (I'm confused)
Hi, My CSM informed us that window functions are enabled. I would like to write the following via ETL, or a My SQL dataflow (either way is fine), but I don't know whether to pick a Table or SQL transform, and either one I pick will not accept the following statement. SELECT *, sum(a) over (partition by b order by a range…
-
Calculating percent of total with values that count in multiple categories
I'm trying to develop a card for diverse business participation reporting. A single business may have multiple diversity classifications, and can be counted towards each. However, when we are looking at the total, we are looking at the total of distinct businesses. Example data: I want the card to have flexibility to…
-
Can I nest a Select Distinct function in a beastmode?
Relatively new to all of this, but I'm working on a beastmode that calculates the average tenure of active employees within my company. The data I'm pulling from includes a lot of duplicate system id's (employee id's). I need to filter out these duplicates in order to get an accurate average. I've written the formula in a…
-
Rolling 30 Day Formula in magic ETL
Hi, I am trying to write the formula above within magic ETL. Column A consists of static values. Column B starts with 0 as the first value and has the formula shown in the image. The formula resets its count every time it gets to a number greater than or equal to 30. The only way I can find to do this is with a LAG()…
-
HTML Table with Numbers
Hello, I was wanting to have more customization when making my tables in analyzer since text cannot change much. I am having trouble making the html work for my numbers though since everything gets formatted as text. I was wondering if anybody had any ideas to set up this code? I found some solutions on the web, but was…
-
Create a summary number as calculation aggregate sum expense minus aggregate sum reimbursements
Hello, I am trying to create a beast mode calculation to show balance remaining after (sum of all expenditure) - (sum of all reimbursements). here is my current calculation. (case when `Expense Type` = 'Expenditure' then sum(`Account Balance`) else 0 end) - (case when `Expense Type` = 'Reimbursement' then sum(`Account…
-
Group in Beast Mode
Is there a calculated field in beast mode to group names for a filter? For example, data looks like this on card slicer: Customer A- UK Customer A-USA Customer A- Mexico Customer B- UK Customer B-USA Customer B- Mexico Is there way of grouping Customer As and Customer Bs together to show on the card slicer only the…
-
Is there a way to create Running Total using Beast Mode?
Hi, I'm building a table card and I would like to create a column that performs a cumulative sum, like the following: | Week | Amount | Running Total | 1 | 100 | 100 | | 2 | 50 | 150 | I tried to do this using SQL Dataflow, it works fine. However, I want to be able to add filters that modifies the Running Total and thus,…
-
Average
I have created a Card for volume by hour. Now the department wants a card Average volume by hour. COUNT(`Case Number`) OVER (PARTITION BY HOUR(`Created Date`)) I'm not sure if I'm doing it right as it's creating duplicate Hours and Volume. I have done it in ETL and getting the result but then I cant do filters as It's a…
-
Number of Months Left As Displayed in Table
Is there a dynamic beastmode that counts the number of months displayed in a table?
-
Rolling Percentage
I want to have a rolling percentage of a value across a series of values. I want to be able to choose the value and the size of the series. Here is an example where I want to know the percentage of Oranges for the last 5 values. Also, Is it possible to do a running percentage of the value? Just thought of that. Ideally I…
-
Beastmode YoY Variance Calc
Hi All, I am working on a beastmode that I thought would be relatively straight forward, but is giving me some issues. I am trying to do a YoY variance calc starting in 2022. The problem I am running into is that the 2021 numbers are not being subtracted out of the 2022 numbers. When I look at the previous year info, it's…
-
Count snapshot data (for HR Turnover)
I am trying to find a way to count number of rows per month (spanning over 3 years and counting) to get the total number employees for each month in variable form. The data structure is set in a way that the number of rows should be equal to the number of active employees as of a certain snapshot date. I am not sure if I…
-
Beast Mode formula: not calculating/populating
Hi - i created several Beast Mode formulas (TQ, LQ, QoQ). I have no issues adding TQ and LQ to a Pivot Table chart. When I add QoQ beast mode to the Pivot Table, no values are populated. The QoQ formula is a difference of TQ and LQ. I can add TQ to TQ, or LQ to LQ and get a result. I just don't get a result if I mix TQ and…
-
Create time column for Line chart
Hey guys! So I want to create a line chart that shows the sum of a total over the course of a 24 hour time period. I have a timestamp column where I extracted the time but tbh it looks weird. So I was thinking to improve the chart, is to create a column that include all time over a 24 hour period (not military time) and…
-
Beastmode adding multiple different values from the same column
I have a marketing channel column. That has a dozen descriptions from all of our different marketing channels. I am only trying to take the count of a few of them together i.e. Yelp, Facebook, google ads, etc... How could I write a beastmode that I can pull out only the channels that I want to add together?
-
Trying to add percentages in a pivot table
Hi, Would anyone be able to help me with the following: I'd like to have my pivot also show percentages of these two columns, I managed to get it to look like the bottom part however when I add a new column in the pivot it says the processing has not worked. Not sure how to add in the percentage? Thank you!
-
Beast Mode Validated but not working
Hi all! My beastmode here is validated but it shows a message "An issue has occurred during processing. We are unable to complete the request at this time. " This is my beastmode: SUM(`Real Produksi TBS Tanaman Produktif (ton)`) / (SUM((`RKAP Luas TM (ha)`) / (COUNT(DISTINCT `Date`)))) So basically the context is: "Real…
-
Simple Addition and Subtraction
Hi There, I am having trouble in returning correct values with a simple addition and subtraction beast mode. It appears that any column with a blank value will not return a end value. And any row with values in all columns calculates properly. Any suggestions on how to calculate to consider blank values as zero value?…
-
Rolling 3 Mo Avg - Breaks Card
I want to calculate rolling average of the previous 3 months. I was going to do this using windowed lag functions ( (LAG(SUM(`Sales Amount`),1) OVER (ORDER BY `YEARMONTH`)) + (LAG(SUM(`Sales Amount`),2) OVER (ORDER BY `YEARMONTH`)) + (LAG(SUM(`Sales Amount`),3) OVER (ORDER BY `YEARMONTH`)) )/ 3 Each Lag works individually…
-
Nested Beast mode
Hi everyone, I have some data like companyid, journeytype, ratingid and npsRating. With ratingid and npsRating, I usually calculate NPS (net promoting score) in beast mode like this: (((Count(DISTINCT (CASE WHEN ((`npsrating` >= 9) AND (`npsrating` < 11)) THEN `ratingid` END )) - Count(DISTINCT (CASE WHEN (`npsrating` <=…
-
Is there a way to check if a field has more characters than a specific amount?
Hi! I am trying to set up a beast mode function that checks if fields are correctly filled out in our asset tracking software, and lists out any asset that does not meet all requirements (ie fields not filled out or have been auto-generated by the software itself). One thing I am curious about is, I'd like to be able to…
-
Beast Mode Calculation in Summary Number
My beast mode formula goes something like this:- SUM (x) / SUM (y) I am not able to use the above beast mode for my summary number. I am looking to get the average percentages, but the aggregation field is greyed out and I get the message "using aggregation in beast mode formula". Any help will be appreciated. Thank you