-
Is it possible to calculate 6 months of data for a metric?
I am trying to collect Customer data Month over Month data for a metric that uses data from the past 6 months. For example - January 2022 would include customer data from July 2021 to January 2022 February 2022 would include customer data from August 2021 to February 2022 and so on.. Any help is appreciated! Thanks! Vik
-
Drill Path Filter to show Specific Apt's without a Specific Procedure
Hey guys, I'm worried that the answer to my question is that it has to be done in ETL, but I'm hoping it can be done as a beast mode. So the data in question looks similar to what I have below: Doctor | Procedure | Date | Apt # | Patient Dr. B | D0330 | 4/1/22 | 80136 | I. M. Madeup Dr. B | D0126 | 4/1/22 | 80136 | I. M.…
-
Dividing two integer columns that contain 0 values causing incorrect percentage value display (CTR)
Hi all, I've run into an "issue" where I can't seem to get Average/AVG values to calculate correctly when using a custom beast mode. My issue sounds similar to this issue here, where some rows contain a "0" or Null value that messes with the aggregation. When I try to replicate that solution I get another confusing /…
-
Beast mode formula: Case When with distribution points
Hi, I have a question about my beast mode formula concerning a distribution calculation. Currently, I am calculating the distribution points based on the volume. If one product of brand x has been sold in period y, then one customer gets 1 distribution point. It is max 1 distribution point per customer per brand. I have…
-
BeastMode calculation - specifying "previous month"
I am using this formula to calculate terminated employees: COUNT(DISTINCT CASE WHEN `WD Event Type`= 'Termination' AND `WD Record Type`= 'Activity' THEN `WD Employee ID` END) I need to modify it so it will include a number for the PREVIOUS MONTH each time that I use it. End goal: I plan to use this count in a drill path…
-
Drill down card with display certain columns when clicked.
I have a bar chart with different events such as cta_clicked, navigation_clicked, media_interaction. I have to create a drill down card but I want to display certain parameters and not the whole table For example, if I click on cta_clicked it should only display columns (cta_title,cta_link_text,component name) . When I…
-
ETL or beast mode for Dynamic assignment of month based on current date
Hi All, Hope you are doing well..I am trying to convert a plan table as below into the table below based on the current date and the reference table Reference table: So I need to basically map the column names (FC1,FC2,FC3...input table) to fiscal month based on the current date and looking up the reference table for the…
-
How can i write this sql statement in domo?
where a.Customer NOT IN(select distinct Customer from CustomerSalesData where InvoiceDate < '2020-07-01') Statement below is a column I use for an sql query.. ROW_NUMBER() over(partition by a.Customer order by MIN(a.InvoiceDate)) as OrderNum I am getting an error while trying to convert this statement. I am not sure how 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.
-
How to use Escape characters in Beast Modes?
Hi, I am trying to create a web link for a card using a beast mode and some of the values that will be passed through the beast mode contain apostrophes ' . When they are passed through the beast mode the link is broken because the value is cut off early. For example the value 'Square's Lake' is cut off early and ends up…
-
Create a URL with CONCAT and variable in the middle.
CONCAT("https://b2b.officedepot.com/rest/ODServices/api/product/priceAvailability/xml?zipcode=XXXX,"&sku=", `VARIABLE` ,"&billto=XXXX) I am trying to have a cell value where it shows VARIABLE . Everything else is part of a constant URL. I just can't get the variable To be in the middle of the URL. I have worked with others…
-
Is AS valid in Beast mode?
Good afternoon, Can AS be used in Beast Modes to create aliases? It doesn't seem like it but I might also be super bad with SQL. Thanks! -Joe
-
Beastmode. How to create a CONCAT URL with several variables?
Want to build the following link on a card =CONCATENATE("https://b2b.officedepot.com/rest/ODServices/api/product/priceAvailability/xml?zipcode=",`CONSTANT1`,"&sku=",`COLUMN`,"&billto=",`CONSTANT2` ) The URL is a constant as well as "CONSTANT1" and "CONSTANT2", but also need "COLUMN to change depending on the value of the…
-
Calculating existing delays , Off delays and new delays on manufacturing data
Hi All , I am new to Domo and I am trying to create a beast mode for the following type of data... I have a manufacturing plant data on a daily basis and specific vehicle (denoted by sno) belonging to a specific category (mmodel) that can go on a delay due to some issues. Now my Input tab has the snapshot of this data on…
-
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?