Help: Simple Monte Carlo Simulation

[sallyta]

Hello,

Can someone help me create a simple monte carlo simulation workflow on Alteryx? I have no idea where to put the sampling tool or if the sampling tool is even the correct tool to use. I want the workflow to spit out a random time frame (random start date and a random end date). The random start date and the random end date must be within the minimum and maximum dates provided by the connected data. So for example, the max date cannot be in the future year 2030 cause I only have data up to 2024. I created a formula in my workflow to tell the max and min date. The workflow will spit out a set of 100,000 random start and end dates. Then after getting the 100,000 set of random start and random end dates, I want to calculate the total dollars from each set of random start date to random end date. So I would have 100,000 different total dollars.

I thought maybe another way is to just do a simulation of a set of 100,000 total dollars, but then I would be missing the random start and end dates. So I don't think this will work.

Eventually after the Alteryx workflow is successful, I plan to show the data visually in Tableau using a histogram so I can see how often the total dollars fall within a certain range.

I am not sure how to go about this. I watched Youtube videos as well as the Alteryx challenge videos and I also tried to mimic monte carlo examples on this Alteryx community forum, but I am not able to get anywhere since their projects/wants are different from what I want. Theirs look much more complex too.

Thank you,

Sally

[danilang]

Hi @sallyta 

1  Calculate the number of days between min start and max end [diff]

2  use DateTimeAdd([StartDate],RandInt([Diff],"days") to generate the random start [RandomStart]. 

3. use DateTimeAdd([RandomStart],RandInt(DateTimeDiff([EndDate],[RandomStart],"days")),"days")  to generate the random end greater than [RandomStart] and less than max end date

Dan

[sallyta]

@danilang I appreciate you reviewing my question! I originally said "I created a formula in my workflow to tell the max and min date," but this is incorrect. I actually created the max and min date calculation in Tableau. So I need help doing that in Alteryx too in order to work on your Step 1. I apologize. I am new to Alteryx and am struggling

[danilang]

Hi @sallyta 

There's no simple way to get information out of Tableau.  Your easiest option is to recreate the logic that you have in Tableau in your workflow.

Dan

[sallyta]

Dan @danilang - I think I am doing something wrong. Can you review, if possible?

[danilang]

@sallyta 

In Alteryx you can't directly subtract dates to find the difference.  You have to use DatetimeDiff([EndDate],[StartDate],"days")

Dan

[sallyta]

Dan @danilang  - I was able to fix the DatetimeDiff([EndDate],[StartDate],"days"), but I am still getting errors that Random Start is unknown. I am going to restart and try again. I am not sure what I'm doing wrong. A part of me feels bummed cause Alteryx is definitely difficult and I was thinking if I should resort to Excel

[Qiu]

@sallyta 

Maybe we can talk more over some sample input and output?

It seems to be an interesting topic.

[KGT]

Just to add to this, you can get your Min/Max dates from the summarize tool. Add that after your data, to find the Min/Max on your date column.