6 Replies Latest reply on Jan 28, 2019 9:07 PM by Prakash Desai

    Comma Delimiter between values with pivoted data

    Prakash Desai

      Hello,

       

      I have pivoted the data as shown below.

       

      Is there a any work around to display the Pivoted values with comma separated for each City/Region based on actions.

      When ID=1234 is selected then below output should be displayed.

      Output:

       

      Thanks!

        • 1. Re: Comma Delimiter between values with pivoted data
          Jennifer VonHagel

          Hi Prakash,

           

          I don't know how you could use to Tableau to get the source data actually structured that way, but you can make Tableau display the data fairly close to your screenshot.  Check out this view: If you put Pivot Field Names on Rows, and Pivot Field Values on the Marks card as text, it will list out the values. There's no comma between values though.

           

          Check out Sarah Battersby 's response in this thread Map Tooltip Multiple Values Showing * where she used a Table Calculation to list values in a field separated by a comma. This was in a Map Tooltip.  Maybe something here will apply to displaying your data as you wish (it still won't structure the underlying data with City's listed in a single cell, but I'm guessing you're more concerned about getting the display right).

           

          Best,

          Jennifer

          • 2. Re: Comma Delimiter between values with pivoted data
            Prakash Desai

            Thanks for your inputs Jennifer.

             

            Yes, values will not be comma separated if i put the Pivot Field Values on the marks card as Text.

             

            I have gone through the link which you have provided and was useful to think other solutions. However the solution will work with actual data set instead of Pivot Field Names & Pivot Filed Values. Pls let me know if you have any other work around.

            • 3. Re: Comma Delimiter between values with pivoted data
              Jennifer VonHagel

              Hi Prakash,

               

              To actually structure your data this way in columns or fields as you show in your screenshot - I don't think Tableau Desktop can do that (but I've seen so many creative solutions provided in this forum I hesitate to firmly say it's impossible).

               

              To simply present this data this way - I worked on a Table calculation that will allow you to show commas with the solution I presented before.  With this calculation, you need to put Pivot Field Values on the Detail card of the Marks shelf, and set the Table Calc to Compute Using > Pivot Field Values.

               

              The calc says if a value is the last value in the list, then return the last value alone, otherwise, return each value along with a ', '.

              IF LAST() = 0

              THEN ATTR([Pivot Field Values])

              ELSE ATTR([Pivot Field Values]) + ', '

              END

               

              Workbook is attached.

               

              Best,

              Jennifer

              • 4. Re: Comma Delimiter between values with pivoted data
                Prakash Desai

                It worked...Thank you Jennifer!!!

                 

                Have another question, is there a way to highlight the values based on some criteria with this Pivot structure.

                1. Display Qty which are <50 in Red else Green

                2. Display Status which are having "N" in Red and "Y" in Green

                3. Display Status=N with down arrow and Y with up arrow

                 

                Thanks!

                • 5. Re: Comma Delimiter between values with pivoted data
                  Jennifer VonHagel

                  Hi Prakash,

                   

                  First, to get different colors of text, we need to actually color the text in the Label box.  So I created three calculations for Labels for each color (Black font, Red font, Green font). We want the calculation to return a value if it should be that color and return nothing '' if it should not be that color.

                   

                  Black font should be anything in ID-Pivot, City, or Region:

                   

                  Red font should be for Qty < 50 or Status = N. Here, for the Qty values we do want the text to be comma delimited.  For the Status text (Y N), because we'll add in arrows we do not want the label to have a comma.

                   

                  Green font should be for Qty >= 50 or Status = Y.

                   

                  Now we put all three of these labels on the Marks card as Labels, then go in and Edit the Labels so they line up in the correct order and the font is colored black, red, and green.  Remember that these are all still Table Calculations, and have to be set to Compute Using > Pivot Field Values (and Pivot Field Values needs to be on the level of detail).

                   

                  I don't think there's a good way to get the up/down arrows for your Status row in this view.  There are characters in the Wingdings font that we could use to trick something out, but Tableau Public, Online, and Server won't recognize Wingdings font.  So I'm going to break this into two different sheets. The first will be just like this but we'll filter out Status:

                   

                  Then we'll duplicate this sheet and modify it to Show a record for Status where we can add shapes. This view is set up a little differently. I put Pivot Field Values on the Columns shelf to hold space open for the Y and N to nicely show both the shape and the Label. I changed the marks card to Shape, and put Pivot Field Values on the Shape and Color cards and set them appropriately.  The Red and Green Font Labels still work here, but you could remove them and just put Pivot Field Values on the Label shelf and they will be red and green.

                   

                  Now we can hide the column headers of N and Y by un-checking Show Header in the drop down menu. And we're left with just the Status row:

                   

                  On a dashboard, we can put the two sheets together in a vertical container, and of course set the filter on the dashboard to apply to all the worksheets:

                   

                  Workbook is attached.

                   

                  Best,

                  Jennifer

                  1 of 1 people found this helpful
                  • 6. Re: Comma Delimiter between values with pivoted data
                    Prakash Desai

                    It worked...Thanks Jennifer.