Capture dates within date Range from different data source

Hi, interesting problem indeed:)

Could you please clarify if there is always such a situation that one opportunity leads to one sale?

I mean could there be a situation where an opportunity leads to no sales at all or more than 1 sale?

Please see attached preliminary solution. It achieves the desired output, however it will not work if there is a different number of opportunities and sales for specific activity (e.g. if activityID = 1 has 3 opportunities and 4 sales the workflow wont work properly because 3 != 4)

capture dates.yxmd

Hi @JarekSkudrzyk ,

Apologies for the late reply. Thank you so much for this amazing workflow!.

To answer your questions.

1. Could you please clarify if there is always such a situation that one opportunity leads to one sale?

I mean could there be a situation where an opportunity leads to no sales at all or more than 1 sale?

- The Opportunity and Sales data don't have any direct relationship. Because Sales data is a monthly recurring value which means if I have a Sales in March 2021, the Opportunity or Opportunities that contributed to that Sales were probably won in June 2021 or Oct 2021 or any other months.

For the 3 data sources: Activity, Opportunity and Sales, the company name and Range of Date from the Activity Data will be the only connection. That's why if the Opportunity and Sales dates are within the date range of the Activity, it will be captured.

In regards to this,

"however it will not work if there is a different number of opportunities and sales for specific activity (e.g. if activityID = 1 has 3 opportunities and 4 sales the workflow wont work properly because 3 != 4)"

- For the moment, there is no ID for a specific Activity that will match to a particular opportunity or sales data. Only the Company Name and Range of Dates can connect these 3 data sources. I believe your workflow will work! 😉

If any of the information I mentioned above affect any results or process in the workflow, please let me know and it will be a big help for me in understanding more the Alteryx tools. This community is just amazing!

Many thanks!

Mike

All comments

Hi, interesting problem indeed:)

Could you please clarify if there is always such a situation that one opportunity leads to one sale?

I mean could there be a situation where an opportunity leads to no sales at all or more than 1 sale?

capture dates.yxmd

Hi @JarekSkudrzyk ,

Apologies for the late reply. Thank you so much for this amazing workflow!.

To answer your questions.

1. Could you please clarify if there is always such a situation that one opportunity leads to one sale?

I mean could there be a situation where an opportunity leads to no sales at all or more than 1 sale?

In regards to this,

Many thanks!

Mike

thanks for your kind words:) I also feel that the community is amazing.

I think you should try to run the workflow on some of your data where the number of sales and opportunities is different. I have not tested it, but I think this might not work.

Please let me know if the workflow is not working properly in such circumstances - i ll try to fix it.

regards

No worries @JarekSkudrzyk I will let you know 🙂

Alteryx Capturing within Date Range_Test.xlsx

Hi @JarekSkudrzyk ,

You are absolutely right that it will not work where the number of sales and opportunities is different. I made some modifications on the module that you did in terms of joining the data. It resulted to a repetition of matches which is expected due to the given data.

Please let me know if you find any flaw in the workflow that I modify.

I attached the yxmd and the excel file with the new data source. Thank you!

Best regards,

Mike

capture dates within date range_v2.yxmd

hi, I have not found any flaws.
My only remark is that, as far as I understand it, this set of data is now a collection of possible scenarios (which activity/opportunity might have led to specific sale).

Indeed @JarekSkudrzyk . Activities with different time range affecting opportunities that will produce Sales for a specific company. Thank you!