6 Replies Latest reply on Nov 6, 2018 2:41 PM by John Rivett

    Show Months with No Data – 10.4 version

    John Rivett

      In the attached worksheet (version 10.4) I have a running count over the months and want my view to display the months that have no data. Our fiscal year runs from October to September. To date, there is only data for October and November but nothing for the remaining months of the year. I want Dec – Sept to appear in the visual.

       

      I tried analysis>table layout>show empty columns and that didn’t work. I tried a data blend with no luck. The attached sheet has an Excel data file as the data source but normally it would be a live Oracle data source.

        • 1. Re: Show Months with No Data – 10.4 version
          Deepak Rai

          Try to Convert to continuous months or you can join it with another datasource containing only Months and thenuse it on Columns and your data on Label.

          Thanks

          Deepak

          • 2. Re: Show Months with No Data – 10.4 version
            John Rivett

            I tried the continous dates and that didn't work. I tried another data source with months and linking on another field 'Year' to the primary data source. That didn't work either. 

            • 3. Re: Show Months with No Data – 10.4 version
              Paul Wachtler

              Hi John,

               

              Continuous dates won't work if you only have data for the first two months of your fiscal year.  Tableau doesn't know to add months when there's nothing in the dataset for those months.

               

              To follow-up on what Deepak said about using another datasource, once you create a datasource that only contains each of the 12 months (whether you do that in excel or in a database table) that table, spreadsheet tab, or whatever it is needs to be used as the primary table in your data source. 

               

              You don't want to blend it with your data - rather you need to add it to your data source connection as the primary table, and left join your current data to that table using the "month" field.  This way it will provide Tableau with all of the months, even though there is no data in the other data source for them.

               

              Here is a guide for creating joins in Tableau:

              Join Your Data - Tableau

               

              To make sure that the null values appear, add your month field from the month datasource (that you created) as a continuous date.  Right click on the axis for that month field on the bottom and select "Show Missing Values".

               

              Let me know if you have any questions.

               

              Best,

              Paul

              1 of 1 people found this helpful
              • 4. Re: Show Months with No Data – 10.4 version
                John Rivett

                I've created a primary table in Excel with just months and linked to my custom SQL with a left join on a similar month field.  When I pull in the month from the primary all the months display. When I pull in my measures from the second linked table and filter to the current fiscal year, the remaining months with no data disappear. However, then when I select analysis>table layout>show empty the empty months display again. This worked!! 

                 

                Thank you Paul, and Deepak. This is very helpful and I appreciate it!

                • 5. Re: Show Months with No Data – 10.4 version
                  Paul Wachtler

                  Hi John - I see you've marked your own answer as the correct one.  Can you at least mark mine as helpful?  Thanks

                  • 6. Re: Show Months with No Data – 10.4 version
                    John Rivett

                    Just marked it. Thank you!!