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
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 668 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 874 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 146 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive