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.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive