Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Capture dates within date Range from different data source

Mwatts23
8 - Asteroid

Hi,

 

Good day!

I hope someone can help me in this very challenging task.

I have 3 data sources and I want to capture the Opportunity date and Sales date base on the Activity date range.

Attach is Excel file for the sample data source, guide and the desired output that I want to achieve.

 

Mwatts23_0-1647620650575.png

 

I have not attach a sample workflow as I am still on the process of solving this puzzle.

Please let me know if you have any clarification and I will be happy to answer it.

 

Best regards,

Michael

 

8 REPLIES 8
JarekSkudrzyk
11 - Bolide

@Mwatts23 

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)

JarekSkudrzyk_0-1647638782807.png

 

 

Mwatts23
8 - Asteroid

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

 

 

 

JarekSkudrzyk
11 - Bolide

@Mwatts23 

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

Mwatts23
8 - Asteroid

No worries @JarekSkudrzyk I will let you know 🙂

Mwatts23
8 - Asteroid

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

JarekSkudrzyk
11 - Bolide

@Mwatts23 

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).

Mwatts23
8 - Asteroid

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

JarekSkudrzyk
11 - Bolide

you are welcome:)

Labels