How do I create a calculated column based off information from another dataset?
I have two datasets:
1- contains the rep name and the number of sales they closed for each date.
2 - contains the rep name and the number of calls they made for each date.
In a report, I'm summing up the total number of calls made each week by all reps and dividing it by the number of setters for that week. A rep is considered a setter if they have not closed a sale in the last 30 days. I can easily figure out how to determine the number of setters for the current week but I'm struggling to figure out how to get the number of setters for the weeks previous. This is easily done via a vlookup in Excel by looking up the reps name on the list of sales 30 days prior to the date of the call and then returning true or false depending on whether they had a sale but I cannot seem to figure out how to replicate this in Domo. Any ideas?
Answers
-
You tagged this appropriately. For this solution you will need to join the two datasets either with an append or combine tile in MagicETL. (you could also use a MySQL dataflow if you are more familiar with SQL) From there you can do some window functions to calculate the total sales in prior 30 days, etc.
It's tough to get too technical on the solution without having a sample of your datasets, or a clearer understanding of their structure.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive