I am not aware of a way to generate random numbers in Tableau itself. You might need to use some pass-through SQL (RAWSQL...) functions to acheive this.
I just tried it by connecting to my SQL Server. I created the following calc:
RAWSQLAGG_REAL("SELECT RAND() AS [RandomNumber]" )
It generated a random number, but one per complete data query:
When I refreshed the data the rand value would change, but you can't generate a whole bunch of random numbers using pass-through SQL, it seems.
Andy, I don't have a connection to SQL at the moment - would
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]
be able to be passed through?
Genius - yes that creates unique random numbers (well, it seems to on a first glance). I have not come across NEWID() before. Nice.
It's late in the day to work out how to turn that into a RANDBETWEEN equivalent. Any ideas?
1 of 1 people found this helpful
So I found a SQL source - over 25,000 newid values the distribution looks linear - no clumping, suggesting that on the face of it at least, it's pretty random. Without the ABS, the values range from -2^32 to +2^32, so your randbetween could be something like:
((RAWSQLAGG_REAL("SELECT abs(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]" )/((2^32)/2))*([High Limit]-[Low Limit]))+[Low Limit])
I would guess, but don't know, that the only difference between this and a truly random list is that you will never get repetition of a number, which you could get with a random list of numbers.
As an addendum, if you're using JET (Excel, text), or an extract, then this does not work. The presence of the measure also stops you viewing the data from the SQL database with the error message "project expressions must be scalar"
I can use the Random() calculation to make ranges. I.e., Random() gives a value 0.01-0.99, so if I multiply it by 10 then I can get a random value between 1-10, if I multiply by 100 then I can get 1-100, etc.
I use these for my dashboard mockups and as placeholders. Note that Bora Beran stated that "Random function only works on Tableau Data Engine so the calculation will appear valid if you explicitly created an extract or if Tableau created a temporary extract for you under the covers" here No more RANDOM() function?