5 Replies Latest reply on Sep 22, 2017 4:46 AM by Simon Runc

    Table Across Calculations is Dropping Category Level if it has No Data in a Certain Column

    Stephen Kimel

      When I create a table calculation that is calculating the running total using the Table Across option, it is dropping of categories that are on the color mark if that category does not have any data for a certain column.

       

      In the workbook attached, category D did not have any data in W3, and therefore none of the previous weeks data (W1 and W2) is coming over into W3 for category D. If I take the color mark off, it shows the correct total. If I look at the data in a table view (sheet 2) it shows the correct numbers as well.

      Why does it drop off category D in W3?

      How do I fix this?

       

      Thank you for your help!

        • 1. Re: Table Across Calculations is Dropping Category Level if it has No Data in a Certain Column
          Simon Runc

          hi Stephen,

           

          Good question! and this down to one of the lesser known features in Tableau...that of Data Densification.

           

          In the below GIF notice how we don't have a mark (square) for W3/D...until I add an index() calc in the level of detail. This is one form of data densification, when we have 2 opposing blue pills (rows and columns) and a table calculation, Tableau densifies the data so there is a mark for all combinations.

          Densify Data v2.gif

           

          Now in your table version, as your Running_Sum is a table calculation it is triggering the densification, which is why you get a value in the final cell of the Table version.

           

          So another way to trigger (a different form of) data densification (known as domain completion) is where we have, what is known as, a "Range Aware" Pill. This is a pill (dimension) which knows it's MIN, MAX and increment...there are 2 of these Dates and Bins. Where we get an option to "Show Missing Values" which triggers (faux) marks for all combinations

           

           

          So if in your real world situation your [Week] is really a date, you'll have this option, but if it's like this we can still get it to work....

           

          First I created an INT version of your week;

           

          [Week INT]

          INT(RIGHT([Week],1))

           

          and then I create a Bin of size = 1 on this field

           

          I can then use this on the Axis, and tick the "show missing values" to trigger the densification.

           

          Hope that makes sense, and solves your problem...let me know if not

          • 2. Re: Table Across Calculations is Dropping Category Level if it has No Data in a Certain Column
            Stephen Kimel

            Hi Simon!

             

            Thank you very much for taking the time to answer my question!

             

            The weeks that we use are not dates, but they do have the year in front of the week like 2017W1. I added some more data to the workbook to make it like the real world situation that I have. I am sorry, I should have added this before, but I didn't foresee the solution.

             

            I tried to replicate what you did with how our data is, and while I could create the bins, it put a lot of extra bins between the end of year 2017 and the beginning of year 2018 (this would happen with 52 weeks as well). If I only look at one year at a time, it works correctly, and I added aliases to the field so that it shows up like I want it. This works well except that I will have to add an alias to the field every week, which is not ideal.

             

            There are two questions that I have with your solution:

            Is there a way to display the column labels the same as they are in the original field without using aliases on the binned field?

            Is there a way to create the bins so that they don't add the extra bins (see SR with Data Densification 2 (not right)).

            • 3. Re: Table Across Calculations is Dropping Category Level if it has No Data in a Certain Column
              Simon Runc

              hi Stephen,

               

              So yes I think we can get around this...

               

              Firstly we can turn your Weeks into actual Dates....I've assumed that 2017 year starts on 02/01/2017 (in my formula, if that's not the case just change this to your start week). I've also done this using several calculations, to help you see what each part is doing. In your final version you can nest these into one calculation

               

              I first create a Year INT

              [Year INT]

              INT(LEFT([Week],4))

               

              and then I create from this a "multiplier" for year (this will become apparent why we do this in a bit!)

              [Year Multiplier from 2017]

               

              and now I can turn your weeks into dates

              [Week Year As Date]

              DATE(DATEADD('week',([Week INT]-1)+(52*[Year Multiplier from 2017]),#02/01/2017#))

               

              and this looks like this

               

              Once we have this, we can just this field...and we can use the display formatting

               

              And now our Week Pill is a Date, Tableau densifies the marks

               

              ...Something odd is that we don't need to use the "Show Missing Values" for the marks to densify; This is why Data Densification is not well documented or known about...some of it (like the "Show Missing Values") is a (deliberate) feature, while other parts (the opposing pills and table calculation) seem accidental! so there isn't just one rule...it depends on the pill types, the arrangement and the Table Calc. But's it's working here, so no need to think to deeply about the whys!

               

              Hope that does the trick!

              • 4. Re: Table Across Calculations is Dropping Category Level if it has No Data in a Certain Column
                Stephen Kimel

                Hi Simon,

                 

                Thank you very much for this thorough answer. I learned more than just my original question. This does work for me in my real environment. As your first reply answered my more specific question, I marked that one as correct. The follow up helps me display the value like I wanted.

                 

                Thank you!

                • 5. Re: Table Across Calculations is Dropping Category Level if it has No Data in a Certain Column
                  Simon Runc

                  Great news!

                   

                  Yes this is one of the most advanced things in Tableau...so may take some time to bed in.

                   

                  For a much more detailed explanation of what is going on, and the various forms of densification & what triggers them I highly recommend this video

                   

                  Tableau Request Live - Data Densification on Vimeo

                   

                  It's a little old now, but the concepts hold.