7 Replies Latest reply on Oct 28, 2016 8:24 PM by LR Qin

    Help: how to show the bar with no data in side by side bar chart

    LR Qin

      Hi,

       

      I am working on survey data and created side by side bar comparing the pre and post response rate (Sheet 1). However, the bar with no data can not show (e.g. only pre1 bar shows in "I don't think so"). I tried Table layout-->Show empty columns. It did not work. Can anybody tell me how to show the missing bars (e.g. to show post1 in "I don't think so")? Thank you very much.

       

      By the way, I want to move the header "Null, I don't know, I don't think so, Maybe, No, Yes" to the bottom of the graph. Currently, I created a new graph in sheet 2 and will put them together to show the header at bottom. If there is a better way to do it.

        • 1. Re: Help: how to show the bar with no data in side by side bar chart
          Stephen Rizzo

          Regarding your formatting question, I don't know of a better way of the top of my head. Depending on what you want to do with this - more specifically, if it is going to be a static chart - you could tinker around with dashboard text boxes and/or annotations on the chart itself. Someone else might have a better solution...

           

          For your first question, you don't have any rows in your data with both Post1 and "I don't think so", so Tableau doesn't know that that was a possibility. The solution for that problem is "scaffolding" - create a data set with all possible measure-value combinations, then join/blend that to your data.

          • 2. Re: Help: how to show the bar with no data in side by side bar chart
            LR Qin

            Thank you for your reply. However, I even can not try the solution since I can not do data blending on this computer now. I got the error message "Unable to materialize temporary table". Tableau support has not replied my email yet. I am wondering if you can try to do it. I just want to know if it really works. Thank you.

            • 3. Re: Help: how to show the bar with no data in side by side bar chart
              Stephen Rizzo

              The numbers seem to be a bit off, but this should illustrate the concept. You will need to re-code the nulls so that the join functions properly in order to get the correct values.

               

              EDIT: Not just the nulls, apparently. The nulls, "I don't think so", and "I don't know" need to be exact matches in the scaffolding spreadsheet in order for this to work.

              • 4. Re: Help: how to show the bar with no data in side by side bar chart
                LR Qin

                Thank you very much. It seems that it works. Can you tell me how you recode the nulls?

                • 5. Re: Help: how to show the bar with no data in side by side bar chart
                  LR Qin

                  Thank you for your edit. There are 6 rows (three each for pre and post) marked as nulls, "I don't think so", and "I don't know" under value with ID "Null" in data set. I think this is what you did. But it brings extra section for "I don't think so" when I tried it. In your workbook, the column for pre1 in "I don't think so" is missing. In fact, there are a lot of questions in the real data set. Your method might not work. I will continue to work on it and let you know the result.

                  • 6. Re: Help: how to show the bar with no data in side by side bar chart
                    Stephen Rizzo

                    Yes, it does bring in Post1 for "I don't think so". I was assuming that was what you wanted. Maybe I misunderstood your question?

                     

                    What I did was create the "scaffolding data set" - an Excel file with all possible value-measure combinations, with each combination as its own row. I then did a left join on your data, linking on the measure and value fields (I did have to pivot your data in Excel as opposed to in Tableau in order to get that to work).

                     

                    What this accomplishes is that for any measure-value combinations that weren't in your data set (such as "I don't think so" - Post1), the left join guarantees that that combination will have a row in your data set with nulls where the joined data would be. When you do your distinct count of IDs, nulls are not included, so the extra added rows don't impact the results.

                     

                    As for re-coding, you probably noticed that the ID counts for Null, I don't know, and I don't think so were off. The most reliable fix would be to replace your survey responses with numbers, then either have a lookup table that you join in to decode them or use Tableau's alias feature.

                    • 7. Re: Help: how to show the bar with no data in side by side bar chart
                      LR Qin

                      Thank you for your reply. I will try this on Monday.