-
ETL Calculation
Please see above scenario. Those are quotes tied to a project. You can see that quote 90795(column D) is a first revision of quote 90439. My goal is to create a calculation that would show binary output as shown in column E. Basically check every 'Project Name' and if there is a revision to a quote (marked in column B),…
-
YTD vs Year Prior
Hi guys, Looked in other posts here but couldn't find the exact answer. I'm creating a table card that would show YTD vs Year prior (Bar charts are not an option since comparing many different categories. The best I could find to show Year prior is SUM(CASE WHEN YEAR(DocDate)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND…
-
Creating a marker and deduping in ETL
Hey guys, Hope you all are doing well. I have an issue with data transformation in the ETL. It is best described on the screenshot below. I have multiple entries for duplicate IDs (one row for each product) and want to mark all entries for a given ID that contain Product 'c' as 1. That's the formula I used below to…
-
Beast Mode
Hey Guys, Coming today with a beast mode question. See attached screenshot for reference. I have a dataset that looks like the attached and want to calculate the value of those ID's. Here is a catch, I want to count only EITHER unique ID's (those that do not have any Revisions), or the latest revision for a given quote.…
-
Keeping only the latest date for unique ID
Hi Guys, I have a question related to ETL transformation. I have a dataset including unique ID's and dates assigned to it. In many cases, there is multiple dates associated with the same unique ID. In my case I want to keep only the latest date associated with each ID. For example I have ID 2 with a date 10.18.2023. If my…
-
Beast Mode calculation
Hello, I'm creating a visualization that aims to show orders that have a specific piece of equipment in it. My database has multiple rows of 'SO #', 1 row for a different type of equipment ordered. I want to see how many orders out the total have number '100' in column 'Equipment #'. In other words, for each 'SO #' I want…
-
Extracting digits from the String
Hello, I'm trying to create a beast mode that would allow me to extract 5 digits that are always coming AFTER words 'Purchase Order'. My issue with it, there is a variable number of digits before depending on the row of data. As an example string might looks like: ABCDSFF Purchase Order 12345 duewobfd ADS Purchase Order…
-
datediff at row levels
Hello, I'm doing analysis of our repetitive customers. I want to create a calculation that will show the days difference between order 1 and order 2 based on the company. Each order has its own row. I created rank column, that assigns 1 to first order, 2 to the second etc… How can I create a calculation that will subtract…
-
Partition BY, filling missing values
Hello, I have sales data and want to do some calculations on reference quote numbers assigned to those sales. Sales data have unique 'docnum' and there might be 5-15 rows assigned to the same 'docnum', depending on how much equipment is being ordered. If there is a quote behind an order, then 'Quoteref' will have the…
-
Ranking card
Hello, I'm comparing companies that my company is doing business with. I'll be using sales as as the comperative metrics and those cards will be distributed on monthly basis to the companies. My question is: is it possible to create a card based on the sales but it will be displayed as ranking? (info is sensitive and don't…
-
ETL Calculation
Hello, I have SO dataset and want to create a calculated field that would show the sum of the items within an order. To give an example, I have an order that has 5 rows of information (5 different items), I want to create another field that would sum up the cost of the items and create another field that would show the…
-
Combining 2 datasets together
Hello, I'm comparing quotes against sales data. They are both powered by separate dataset. Each quote has a unique ID that is later being copied into sales dataset names as quote #, if that quote turned into sale. I appended both datasets together and now I'm trying to write a formula that would show me which quotes turned…
-
Replacing value in case statement
Hello, In my dataset each account is assigned to state. In some cases I want to manually adjust the state. I tried to do in in ETL. I created a constant called State with empty string. Then I added a formula tile and created this case statement: (original state is what I had originally assigned to state) (CASE WHEN…
-
Excluding weekends from a prior day card
Hello, I'm build a prior day card that as name says will be depicting a prior day data. Business operates Monday-Friday but prior day on Monday would show no data since it is using Sunday's information. Is there a syntax I could write or other transformation that would exclude weekends from being included in last day and…
-
Assigning people based on the ZIP
Hello, I'm coming with a following question. I'm working with inconsistent dataset. My Domo dataset has an individual assigned to a sale based on the ZIP within a region, which is incorrect. Within that region there is more than 1 person who sales there, that info is based on the ZIP code. I have the correct ZIP code file…
-
Data imputation
Hello, I'm working with a large dataset that needs some data imputation and got some questions around it. I want to filter my data based on location but a small amount of the dataset is null there. Luckily, it is sales order data, so the same SO has many orders to it and SOME of them are lacking the location information.…
-
Sum multiple values for the same Invoices
Hello, I have a dataset that contains multiple rows of orders. I got an account number that represents the same client but the same client can have multiple orders. I would like to create a transformation that would SUM revenues and expenses that are all assigned to the same account number that represent 1 unique customer.…
-
New Customers/
Hello, I'm working on visualizations that aim to show new customers within years. My database contains data from 2020 moving forward. I want to see new/unique customers that bought from us for the first team this year. If it is possibly want to compare it to 2021 too but struggle to filter that out or write a beast mode.…
-
Visualizations don't fit into report
I created some pivot/html tables for reporting purposes but they are relatively wide with a few rows. When I try to export it as a report columns toward the right hand side of the table are being cut out from the report. Is there a way to adjust the width of the table so it fits into the exported page width of the report?…
