3 Replies Latest reply on Sep 24, 2018 7:49 AM by Bethany McDowell

    Table showing values and totals when none exists

    Bethany McDowell

      Hello,

       

      I'm working on a project in which I need to be able to apply a year filter and see the percentages for that time frame.   I have several problems:

       

      1.  My year filter is showing #missing years.  If I filter on #missing its showing me what data it is.  However, there is NO underlying data for these fields.    

      2.  When this filter is applied to #missing years, it's also showing a column total when there is no data.  There is data in the years that are filtered OUT.   If I show all, there is a row that has data and the total is 100%.

      3.  I need these percentages to change based on previous years.   For example: If I select, 2017 then I want to see the percentage that reflects 2017 which would include any previous years.   I need it to recalculate he new "count" of underlying data.

       

      I've attached a sample PDF of what I'm seeing. 

       

      Thank you.

        • 1. Re: Table showing values and totals when none exists
          Naveen B

          Hi Bethany,

           

          1) Reason you are getting missing years because your sheet have all underlying data because you use lookup calcualtion

           

          2) Because your table calculation getting applied table across and table down

           

          3) It can be doable

           

          but only one thing how you are data structured along with the table calculation

           

          Could you please recreate your scenario using tableau superstore data along with what would be the O/P

           

          If needed duplicate the lookup field which you place in filter and select missing values exclude

           

          and show the filter on original field --> select show relevant values

           

          You will get the data when year selected along with percentages included in previous years

           

          Hope this helps

           

          BR,

          NB

          • 2. Re: Table showing values and totals when none exists
            Bethany McDowell

            Hi Naveen,

             

            I did my best trying to recreate my original file.  I couldn't get a calculation into the Year field for some reason to show what I did in my original file.  But, you were correct that it was a lookup calculation.  LOOKUP(MAX[Year]),0)

             

            Also my original data is year only.  Not month and day.  Wasn't sure how to fix that to show the missing.

             

            I'm a very new user with a big project request and this one thing has slowed me down.

             

            Thank you for any help you can give.

             

            Bethany

            • 3. Re: Table showing values and totals when none exists
              Bethany McDowell

              Hi Naveen,

               

              Please see my previous reply.  I'm still stumped on this.

               

              Thank you.