7 Replies Latest reply on Sep 15, 2018 3:08 PM by Ken Flerlage

    generate a random value for each record

    dmitry.beransky

      I'm playing around with making a gauge chart with a pie chart.  So far I've been pretty successful, the only remaining issue is that I have to generate a random index number for each slice outside of tableau.  It's the index column in the attached sample workbook.  Any ideas how I replace 'index' with a calculated field?

        • 1. Re: generate a random value for each record
          Ken Flerlage

          What exactly is the purpose of the index field?

           

          BTW. Here are a couple of similar things I have written about. Some of the techniques used here might come in handy for what you're creating.

           

          Creating NPS Gauges in Tableau - Ken Flerlage: Analytics Architecture, Strategy, & Visualization

          Percentage Gauges in Tableau - Ken Flerlage: Analytics Architecture, Strategy, & Visualization

          • 2. Re: generate a random value for each record
            dmitry.beransky

            The index field sets up a range of slice ids that will make up my gauge chart.  For example, if I want the total range be represented by 8 slices, the index range will be from -8 to 7.  Where slices with negative values will end up on the bottom of the pie chart and will be rendered as hidden.

             

            reindex -- will shift the index values up by 3/4 of total slice count in order to position slices for proper rendering (i.e. rotate slices by 90deg clockwise).

             

            bucket label -- maps from the index to the actual value that the slice is supposed to represent. 

             

            With this approach I can easily change the resolution of the gauge as well as implement a linear or a logarithmic scale just by changing the formula for "bucket label"

            • 3. Re: generate a random value for each record
              Ken Flerlage

              OK, I was confused because you said that index was a random number. But it just seems to me that you need to be able to create a dynamic range of numbers based on the number of slices. Correct?

              • 4. Re: generate a random value for each record
                Ken Flerlage

                So, I think what you need is to create some bins then use index computed along that bins in order to artificially create the number of slices. I didn't take this all the way, but you'll get the idea of how to do it.

                 

                I started with a simple data set with one column and two records:

                 

                I then created a parameter called Slices which will be used to configure the number of visible pie slices.

                 

                Next, I created the following calculated field:

                 

                Range Adjusted

                // Ensure we have the right number of points in our range.

                CASE [Range]

                WHEN 0 THEN 0

                WHEN 1 THEN 2*[Slices]-1

                END

                 

                If, for example, I chose 20 slices, then Range Adjusted will now have two values--0 and 39. This will give me the 40 slices I need (half visible and half invisible).

                 

                Then I created bins on Range Adjusted with a bin size of 1. Next, I created an alternate Index calculated field:

                 

                Index

                // Create our range of indexes.

                INDEX()-[Slices]-1

                 

                Then, on a new sheet, I added Range Adjusted (bin) to detail, followed by Index and Type (your same calculation). Since Index is now used, both are table calculations, so you need to change them to compute using Range Adjusted (bin). We now have something like this:

                 

                 

                I didn't take it any further from here, but I'll just note that you won't be able to sort on the Index field the same way as you did in yours--you'll need another method. My recommendation would be to create some sort of sort calculated field, which has a structure similar to the Type field. Based on the various variables and the Index, it would need to break each of the slices up into one of the following categories (similar to my blog posts I shared earlier):

                 

                 

                 

                Slice 1 – Variable sized slice starting at 0° and ending between 0° and 90°. Could be either green colored or grey.

                Slice 2 – Variable sized slice starting and ending somewhere between 0° and 90°, after slice 1. If everything on the chart is colored, then there would be nothing in this. Otherwise, it would be grey.

                Slice 3 – Hidden slice starting at 90° and ending at 270°. This slice will always be 180° and will always be the same color as the background, rendering it invisible, essentially making it a half-donut.

                Slice 4 – Variable sized slice starting at 270° and ending between 270° and 360°. This slice will be used to show the first part the colored area.

                Slice 5 – Variable sized slice starting and ending somewhere between 270° and 360°, after slice 4. Could be either colored or grey.

                 

                Once you have a calculated field to do that, then you could sort everything based on that.

                 

                Using INDEX() certainly complicates things due to the need to use complex table calculations, but I hope this gives you a push in the right direction.

                • 5. Re: generate a random value for each record
                  Ken Flerlage

                  Following up on this one. Let us know how we can help further.

                  • 6. Re: generate a random value for each record
                    dmitry.beransky

                    I'm sorry, I haven't had a chance yet to get back to working on that report.  Hopefully within a few weeks I'll know whether this approach works for what I'm tyring to do.  Thanks.

                    • 7. Re: generate a random value for each record
                      Ken Flerlage

                      Following up on this one. Let us know how we can help further.