How do I create a calculated column based off information from another dataset?

lb1234
lb1234 Member
edited October 2022 in Magic ETL

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?

Tagged:

Answers

  • ST_Superman
    ST_Superman Domo Employee

    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.