Basically, if you have a table calculation that uses rand() and then want to re-use this column in another table calculation, Looker will re-generate the random number for the new calculation. I attached images below that explain the problem. I haven’t looked at the SQL to see why, but regardless, there should probably a warning in the quickhelp about this . . .
Hey Nathan! Thanks for sharing. Didn’t know about this because I don’t use rand() function on Looker. I tried it personally and got into the same issue as you. I think the problem with table calculation is that it runs the calculation after you hit the run as it doesn’t refer to calculation 1 result but the SQL that was written.
What are you trying to do with this function? Perhaps we can think of alternative solution for your problem. 🙂
Hey! Basically the problem is I have a table with 100 teams and each team has 5-10 people. I want to randomly select 1 person from each team (give them a yes flag) and give all others no flags.
My method was to create a rand() variable for each member of the team, then select the max of the rand values as the yes value. But because of the above error, this method didn’t work. After talking to support, I gave up and just did it in Google Sheets. If you have any ideas I let me know!
+1
It looks to me like what’s going on is that calculation 2 isn’t referencing the actual returned value of calculation 1, but is just taking calculation 1 and inserting it into the expression, then executing it again— since it’s a rand(), those two are different.
I’ll take a gander internally and see if this is the expected behavior. It doesn’t strike me as something unintentional/a bug though at first glance.
I think what would work here here would be to return an actual dimension with a sql: RAND() function, and reference that in your table calculation!
Ah yeah the last suggestion makes sense. I can make a hidden dimension that generates a random value, and use that.
Is the sequence you explain above how all table calculations work? I can’t think of another instance where this would matter because most/all other Looker table calculations are deterministic, just curious! I guess if this is how they work, maybe it would be good to have a function that can take the resulting value of a table calculation and inject that value into a sequential table calculation.
Thanks for the information!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
