RANDOM() function

AnaplanIdeas

Function that will generate random numbers, between specified min & max values.

Excel has two functions to provide random numbers:
RAND() generates a decimal value between 0 and 1
RANDBETWEEN( x , y ) generates an integer >= x and <= y

It may be worth considering an extension to these functions such as:
RANDOM( x , y , z) where z dicatates the increment of allowable values between x and y

+Example:+
RANDOM() = 0.0001, ... 0.9999
RANDOM( 2 , 7 ) = 2, 3, 4, 5, 6, 7
RANDOM( 2 , 7 , 2 ) = 2, 4, 6
RANDOM( 2 , 7 , 0.5 ) = 2.0, 2.5, ... 6.5, 7

 

 

AnaplanIdeas

Status changed to: In Review

duncan_pearson

So there is one main problem with a calculation function that produces different values every time that it is used. In order to calculate efficiently Anaplan will choose to re-calculate particular cells or not according to strategies that are not in control of the customer. This means that your values will change at times that you have no control over, including every time the model is loaded.

 

What would seem to make a lot more sense would be an action, where the admin would choose a data-entry line-item and when (and only when) the action is run it would fill the line-item with random numbers (according to other parameters entered when the action is created and edited).

 

What do people think? Would this serve the purpose as well as a function?

NoahJ

Duncan, I think your proposed solution would fill the need exactly. 
The reason I personally am looking for random number generation within Anaplan is for creating sample data and dashboards - I want to create a dashboard proof of concept that is not just boring flat lines, but not have to go and type different numbers for all the cells. A on request random data generation tool of some sort would be perfect. I would be in support of that feature request.

Guillaume.Auger

Hello Duncan,

For us also this new action "random" would fit our requirement, it would avoid us to have to interface a random list of numbers from an external tool each time we want to run a new calculation.

schulze

I definitely concur with @duncan_pearson -- @NoahJ's use case is exactly my own.

jonpetruch1

This would be highly beneficial in some cases. I see it has been added to back in July 2018, and now in "considered for future roadmap" state. Is there any update? 

Laazizir

@duncan_pearson how can be done technically ?

Tin

Is there any update regarding this one? or is there any workaround for this in Anaplan? Any tips would be of big help. Thank you!

Unknown

This content has been removed.

jepley

Support request for this functionality. Technical solution could be to create an action that populates number-formatted line items only that runs an import that uses max/min constraints from line items. (thinking PlanIQ-esque but hopefully simpler)

GP

linked (updated on) to a date or quarter could be usefull

NoahJ

For anybody still interested in this, I was looking into standard random number generation methods (

Random number generation - Wikipedia

) and realized the most common type could be built in Anaplan quite easily - it requires a "seed" value and the time dimension, but was not hard at all to build! I wrote a brief explanation here:

Build a random number generator in Anaplan

NoahJ

Jul 15, 2024

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:

https://us.v-cdn.net/6037036/uploads/J8OLEMYGGDP9/randomnumbersequence.png

Going more in depth:

However, the formula above has a limitation: the sequence will always be the same. The first value will always be 0.4442 then 0.8598 then 0.1600 then 0.5505, etc.. This is one of the limitations of computing “random” numbers; they depend on “seed” values to start the sequence. Given the same seed values, the results will be the same. Additionally, the sequence will eventually repeat, but should be plenty long enough for most applications.

If you want unique 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), 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:

• Linear congruential generator - Wikipedia
  
• Random number generation - Wikipedia
  

Hopefully this method is useful to you, or at the very least interesting!

Questions? Leave a comment!

---

View Post