7 Replies Latest reply on Jan 26, 2017 2:20 PM by David Romanowski

    Display a single list column into two columns?

    David Romanowski

      Hi Guys,

       

      Bit of an odd request here.  I am looking for a way to display a list of values from a single column style into a two column list (or multi-column).

       

      Have a client that wishes to display a list of values under a heat map on a portrait layout.  The values listed are based on the same filters from this heat map.  I guess the aim here is to reduce amount of white space and also display values conveniently when the list of values grow large (30+).  The filters will be user driven so I need that list to change dynamically with the heatmap

       

      I have attempted to replicate a mock example from superstore data and have attached the workbook. Also see screenshot below on what I am also attempting to achieve.  Hope that makes sense? Let me know if you have any questions

       

      I'm open for ideas on how this could be achieved...

       

      MockList.png

       

      Thanks

      David

        • 1. Re: Display a single list column into two columns?
          Dan Sanchez

          Hi David!

           

          My initial thought would be to use two separate worksheets and leverage the INDEX() and SIZE() table calculation functions to tell Tableau which rows to place in which worksheet.

           

          I've attached a revised copy of the workbook where I duplicated the CustomerList worksheet and created a 'row filter' calculated field that uses the following formula:

           

          [index] <= ([size] / 2)

           

          The calculated field is then placed onto the Filters shelf and set to show True values for CustomerList col1 worksheet, and False values for CustomerList col2 worksheet.  Additionally, the 'row filter' calculated field has the advanced Compute using settings configured by right-clicking the field on the view and selecting Edit Table Calculation.  I have then selected Advanced for Compute Using and set Customer ID and Customer Name under the Addressing column.

           

          By using ([size] / 2) as the formula to filter with, we can force the first column to show the first half of the rows, and the second column will display the second half of the rows.  As long as the client wants to always split the rows equally between the two columns, this configuration should accomplish that.

           

          If there are any questions let me know.  Thanks David!

          • 2. Re: Display a single list column into two columns?
            David Romanowski

            Dan the man!

             

            This solution is perfect for what we need.  Thank you!

             

            I just had to fiddle around to get the advanced compute feature going for Index and Size function.  So this is now working quite neatly in the real workbook.

             

            Just wondering how might we be able to tweak the [index] <= ([size] / 2) formula so that when we have an odd number size, the +1 row will fall into the first worksheet?  E.g. If we have a size 21 list, how can we force the first 11 to go into sheet 1 and the last 10 in the second?  At the moment 10 are going into sheet 1, and 11 into sheet 2.  It would be good to keep the order shown correctly

             

            Regards

            David

            • 3. Re: Display a single list column into two columns?
              David Romanowski

              Hi,

               

              Tinkered around with the formula - seems like the below puts the higher row count into first sheet. Maybe it can be simplified? either way, it works for me.

               

              [index] <=

              IF ([size]%([size] / 2))= 0

              THEN [size] / 2

              ELSE [size] / 2 + 0.5

              END

               

              Cheers

              David

              • 4. Re: Display a single list column into two columns?
                Dan Sanchez

                Hi David!

                 

                That's a great question and one that requires a fun solution!

                 

                In the sample workbook I took the SUM(Number of Records) field off of the Filters shelf and noticed that we have an uneven number of Customer IDs which was great news as I did not have to create a new data source!

                 

                So it looks like that data source has 3403 Customer IDs, so we need to show 1702 in the first column and 1701 in the second column.

                 

                For this type of issue we can use the Modulo operator (%) to check whether the total number of Customer IDs in the view is Even or Odd by using the the following formula:

                 

                "[size] % 2"

                 

                For values that are Odd the above formula will return a value of 1 and if the value is Even, the above formula will not return anything.  With this in mind, we can modify our 'row filter' calculated field to use a slightly different formula as below:

                 

                IF [size] % 2 = 1

                    THEN [index] <= ([size] / 2) + 1

                    ELSE [index] <= ([size] / 2)

                END

                 

                We will then place the new calculated field (I have named mine 'row filter + 1 on first sheet' in the new workbook) onto the Filters shelf and use the same Compute Using settings as before.  The col1 worksheet will set the Filter value to show True and the col2 worksheet will set the Filter value to show False.  Let me know if there are any questions with the new filters.

                 

                Thanks David!

                1 of 1 people found this helpful
                • 5. Re: Display a single list column into two columns?
                  David Romanowski

                  Aah! So that's how the % mod works in Tableau.  I was getting confused, expecting it to return an actual value rather than a flagged response

                   

                  You've explained that really well and provided a neat formula which I've now changed to!

                   

                  Again, thank you for your help Dan.

                   

                  David

                  • 6. Re: Display a single list column into two columns?
                    Mohamad Russzaimie

                    hello David, can u help me,, i need to separated the title of specific date , for example 1100-0000 represent 24 January and 0100-1000 represent 25 January, how to do so David. and another question, i need to to change the number 0000 for 2400, can u help me please  David

                    • 7. Re: Display a single list column into two columns?
                      David Romanowski

                      Hi Mohamad,

                      I'm not sure if I can help you with the translation of numbers into dates?  Have you tried starting a new post and putting together a packaged workbook that demonstrates the data?  I'm sure there will be someone within the Tableau community who can help providing more information is provided

                      Regards,

                      David