12 Replies Latest reply on Aug 2, 2016 11:21 AM by Julianna Wokurka

    random value for each row

    Julianna Wokurka

      I want to use the random() function in Tableau to create a random value for each row so I can jitter the locations. However, Tableau is not doing that. It's creating a random value for each zip code instead. Any ideas on what the problem is? Thanks, workbook example attached (workbook created in most recent version of Tableau)

        • 1. Re: random value for each row
          Shawn Wallwork

          Your 'Level of Detail' is too high (ZIPs). If you put [Unique RowID] into the viz (on the Detail shelf) then your RANDOM() will probably give you what.

           

          --Shawn

          • 2. Re: random value for each row
            Julianna Wokurka

            I already tried this and it was not successful.

            • 3. Re: random value for each row
              Shawn Wallwork

              How did you try this? In your attached workbook which is the Unique Row ID field?

              And yet you have 61 records:

              Meaning that there is not currently a Dimension we can use to slice all 61 records into unique marks. Provide a unique row ID as a dimension, drop it on the detail shelf and your RANDOM() has a chance of working. But if you can't get down to the record level (63), then they won't ever slice/dice up.

               

              --Shawn

              • 4. Re: random value for each row
                Julianna Wokurka

                I did not do it in the attached workbook. I tried it before I submitted a question. However, to prove it did not work, I tried it on my dataset which has 7,409 rows of data. Here are the images to follow. Note: I included a unique ID for each row and dropped this on detail. Still, zip codes have the same random number created.

                • 5. Re: random value for each row
                  Shawn Wallwork

                  Julianna, so you're really going to make me work for this one!

                   

                  So first using RANDOM() is not really (probably) the best way to deal with jittering. There are much better formulas for Jittering. I'm sure you've Googled: Tableau Software Jittering and found the Alan Eldridge link.

                   

                  But I'll go ahead and add a unique row ID field to your data and then import it back in and see if I can't make your RANDOM() function concept work. No promises, because random really does mean random (mostly) and so there will always be a chance you end up with numbers very close together and produce overlapping points.

                   

                  Just curious, are you a) trying to solve a problem (spreading marks), or are you b) trying to prove a solution (using RANDOM()) to solve the problem?

                   

                  --Shawn

                  • 6. Re: random value for each row
                    Shinichiro Murakami

                    I don't quite understand your final goal, but here is what I know of.

                     

                    I don't think we can draw Jitter type chart on Fieldmap or on continuous X-Y axis.

                    To show the number of each Location category in specific areas, only the way I know of is using Pie chart.

                    Which look like this.

                    Or much rougher  area group,

                     

                    Jitter type chart is only available on the Discrete X-Y axis, which is not on exact field map, but by these tiled map.

                     

                    Workbook: U.S. Data Science Programs

                     

                    Thanks,

                    Shin

                    • 7. Re: random value for each row
                      Julianna Wokurka

                      Hi Shawn,

                       

                      My only goal is to jitter the points on the map so they do not appear exactly on top of one another.

                       

                      I've seen someone else recently use random() in Tableau to easily jitter the lat/long to make a nice dot density map. All I want to do is recreate it with my data. You can see from my screen shots that I added the unique ID, but the zip code 44425 has the same random number generated 4 times. However, each row has a different ID for each zip code.

                       

                      I've seen the link you mentioned. However, just jittering the lat/long with a random number seems much easier and intuitive. Since I couldn't get it to work in Tableau, I decided to just create the random numbers in excel and I found it worked fine. In fact, here's how it turned out:

                       

                      But it really bugs me I can't get the random() function to work for me in Tableau. I suppose the better option would be to give up and try this other method. But I'm pretty stubborn and I'd like to understand why it's being so fussy.

                      • 8. Re: random value for each row
                        Yuriy Fal

                        Hi Julianna,

                         

                        Tableau RANDOM() function works as intended.

                        The key is to use it at the right granularity.

                        This could be every single row in your dataset.

                        For that you outta have a [Row ID] field in your dataset

                        (both Shawn and Shin are making points about it).

                         

                        The second point is that RANDOM() function could be used

                        in a different kind of calculations -- Row-Level, Aggregate and Table Calcs.

                        This case implies using a Row-Level one, hence the [Row ID] field.

                         

                        If [Row ID] is present (and actually have unique values for each row),

                        then your [random] calculation does exactly what surgeon general recommends :-)

                        It calculates a unique random number for each row, and your Lat/Lon Jitter just works.

                         

                        As an alternative explaining the concept, I included in the attached another variation of Jitter calc --

                        this one is using RANDOM() and [Row ID] together in a single calc -- to force it to be a Row-Level one.

                         

                        Please find the attached.

                        Hope it could help understanding.

                         

                        Yours,

                        Yuri

                         

                        PS  I'm thrilled to see why having [ID] field doesn't work for you.

                        1 of 1 people found this helpful
                        • 9. Re: random value for each row
                          Julianna Wokurka

                          Yurij,

                           

                          Thanks for your reply, I really appreciate it. The ID field is a unique Row ID field. The ID field has a unique ID for each row - it IS the Row ID. I understand that everyone thinks I should use it, but it's not working and I proved this in my previous entry. I'm hoping one of your tricks may be the answer to my problem and I appreciate the help from everyone.

                           

                          Thanks,

                          Julianna

                          • 10. Re: random value for each row
                            Julianna Wokurka

                            Great news! It appears to be working when I force it:

                            [Latitude]+((RANDOM()*2-1)/[Random division]) * (float([ID])/float([ID]))

                            It makes me wonder if the ID doesn't work without being forced since it is a string and not a value.

                             

                             

                            However, if I make both latitude and longitude exactly the same type then it looks odd. Below shows if they are both a "dimension" or average. Anyone know what could be causing this oddity?

                            • 11. Re: random value for each row
                              Yuriy Fal

                              Hi Juliana,

                               

                              It looks like the same RANDOM() value is generated (for the same [ID])

                              when both [Lat Jitter] and [Long Jitter] calculations are materialized.

                              Thus jittering occurs along a 45' angle path. If one would change

                              the SIGN in one of the calculations (Lat or Long), the angle changes.

                               

                              Yours,

                              Yuri

                              • 12. Re: random value for each row
                                Julianna Wokurka

                                Got it, thanks for your help with this. As you might be able to tell, I don't use the random() function often. I tried using the other jitter method I saw online, but I couldn't get it to work. This method seems to work just fine with less hassle. Thanks again!