Build a random number generator in Anaplan
Author: Noah Jackson is a Certified Master Anaplanner and Principal Data and Insights Architect at Anaplan.
Anaplan does not have a RANDOM formula or function, but random numbers can be useful — I was interested in it for generating sample data, but it could potentially be useful for other applications such as Monte Carlo simulations. Fortunately, existing formulas can be used to replicate the most common pseudorandom number generation (PRNG) method used in computing. (I will post links to the full explanation of the mathematics behind this method below.)
A sequence of random-seeming numbers and can be generated in Anaplan using the MOD function and the LAG function. It can be built in a single time dimensioned line item:
Random Number Sequence = MOD(15161346 * LAG(Random Number Sequence, 1, 539346) + 25124523, 1000000)/1000000
This will create a sequence of integers between 0 and 1 that seem to vary randomly. Depending on your needs, that one line item might be all you need! I graphed the first 200 values in the sequence:
The integers I hardcoded into the formula are fairly arbitrary - you can use different numbers and it will "re-randomize" the sequence. If the numbers you choose are large there will likely not be any noticeable repeating patterns, but you can experiment with different values!
Going more in depth:
Computational methods of generating random numbers have a limitation: they depend on a "seed" value, or some external starting point. Many systems disguise this fact (i.e. using "current time in nanoseconds" as the seed value), but it is still a requirement. Additionally, the sequence will eventually repeat, but can easily contain a very high number of values such that it is not noticeable.
For the formula I provided, the first value will always be 0.4442 then 0.8598 then 0.1600 then 0.5505, etc.. The sequence is defined by the integers I hardcoded into the formula. Given the same seed values, the results will be the same.
If you want changing and unpredictable values, you will need to modify one of the large integers in my original formula — either manually, or by setting them as a reference to a separate line item.
It is most common to modify the last value in the LAG formula (539346, in my formula), as modifying the other values can sometimes create an obvious repeating pattern in your results. If you point it at something that can be changed via import, then every time you run the action you will get a new sequence of results.
If you want a deeper understanding of randomness in computation than I can give here, or to understand the factors for how the MOD function creates pseudo random results, you can read more here:
Hopefully this method is useful to you, or at the very least interesting!
Questions? Leave a comment!
Comments
-
It is also possible to implement this without the time dimension. It uses the RANK formula, so has a limitation of 50 million cells, but is very easy to implement. It can be done in just two line items - it could be combined into one if necessary, but would become more difficult to understand so I will write them separately. The line items are:
Item Rank = RANK(1, ASCENDING, SEQUENTIAL)
Random = MOD(15161346 * (539346 + Item Rank) + 25124523, 1000000) / 1000000
Additionally, you could combine the methods if you had lists and time dimension with something like:
Random = MOD(15161346 * LAG(Random Number Sequence, 1, 539346 + Item Rank) + 25124523, 1000000)/1000000
Again, the values will appear random but will always be the same unless you change the large integers I hardcoded into my formula.
For anyone curious about the theory behind the method - using the time dimension uses a single "seed" value to create a sequence of many numbers each dependent on the previous one, whereas the non-time method uses many seed values (as many as you have items) to each generate a single "step" of the random sequence.
1 -
Also, random numbers can have other applications… like building a model that lets you play Blackjack in Anaplan 😉 the "Next Round" button is just an import that clears the input module and increments the seed value by 1 to re-shuffle the deck!
2 -
Wow, thats super interesting! It has variety of implementations. I guess it is not monte-carlo randomisation, but at least something.
Also it reminded me that back to old days we prototyped with @oleg-zimin poker app. A multiplayer model for turn-based game.
0 -
@nikolay_denisov that looks awesome!! And I just saw a community post asking about what fun stuff people have built with Anaplan - this would fit perfectly!! I'd love to hear more about how you made it work, you should share it there!
0
