5 Replies Latest reply on Jul 9, 2018 3:21 AM by Alex Martino

    How can I add a column inside tableau and populate it

    Alex Martino

      Hello. Steve Martin or anyone

       

      Steve Martin created instruction on how to easily create a waterfall chart with measures. He used superstore set. How can I do what he suggest below?

       

      How do I add a source column to an already existing data set? And then how can I create it so that it will be populated with one null record and all other records as "end"?

       

      Also, does anyone know why this works? Thanks!!

       

      To build the waterfall, the datasource needs an additional source column we shall call 'Waterfall'
      Once in place, you will need at least one null record where the Waterfall column is populated as 'Start', all other actual records need to be populated as 'End'

       

      When filtering, it is imparitve that the waterfall start record (in this instance 'Null') also remains in the filtered items

        • 1. Re: How can I add a column inside tableau and populate it
          Steve Martin

          Hi Alex,

           

          (And for anyone else that may have read this thread)

           

          It is not just a new column that is needed, it is a new row as well and, it is this new row that is the most important feature of the whole piece:

           

          You see, in order to plot measures in this way, this is not some that Tableau can natively do at least not in v8.2 when this workaround was designed, and still not today on v2018.1

           

          As such, whilst it is possible to use a secondary field in the existing set, this is not advised as this will always need to remain in view in order to act as the anchor.

           

          Have you seen the complete instructions with the workbook on my Tableau Public profile? In it I explain exactly what is going on, and why things need to be built in a certain way: Measures waterfall

           

          What you see in the waterfall exists as a facade - a means of creating something from nothing: you see, Tableau has a function that can only be used with continuous dates - if you were to have continuous dates in your plot but, some dates were missing between the start and end, you can select the Show Missing Values feature from the context menu and have Tableau show dates where there actually aren't dates in your set - I capitalise on this:

           

          The additional row, is an anchor, it cannot contain any data save for an indicator under every dimension field, anything you like although I tend to use something like "Waterfall Anchor" such that the superstore sales would look something like:

           

          Region                   Product

          Waterfall Anchor    Waterfall Anchor

          North                      Furniture

          South                     Office Supplies

          East                       Technology

          West                      Furniture

           

          Or, on the public vis, it appears as null.

           

          Then, I work-out how many fields I am going to need, so say I want to show Sales, Profit, Orders, Cancellations and then the Grand Total, this means I need 5 fields.

           

          And then, this is the part you you referenced earlier, I use the additional field, this forms the control between the anchor and the rest of the data: the [Waterfall Definition] Field, for the anchoring row is set to 'Start' (or anything else you want to use that can stand out) and then, all the actual rows in the set or attributed with 'End'

          And then - this is the clever part, I create a new calculation:

           

          [Waterfall Start]

          If [Waterfall Definition]  ='Start' Then Today()

          Else Today()+4

          End

           

          And drag this new field onto the waterfall so what will plot will be two dates - today's date and the date four days from now so assuming today is the 1st, the dates that shall be visible will be:

          01/01/2000 and 05/01/2000

           

          And then, I simply activate the Show Missing Values so Tableau will now plot the missing three dates and will now show as:

          01/01/2000; 02/01/2000; 03/01/2000; 04/01/2000; 05/01/2000

           

          - five individual fields

           

          Everything from this point forward is now an illusion as I now use the Index() to determine what data is plotted where: You see, now, there are 5 fields visible and so, the index will plot to 5, so I can now use this to tell Tableau what to plot on which index eg:

           

          If Index() = 1 Then Total(Sum(Sales))

          ElseIf Index() = 2 Then Total(Sum(Profit))

          etc

           

          Note the use of Total function here - without this, Tableau would plot nothing as there are no dimensions in the set that would tell Tableau (or the underlying source) what needs to be considered so by using the Total, this tells the underlying source to ignore all the underlying dimensions, and to sum the total of sales etc using only the filters to determine the rows of the data in the aggregation.

           

          After this, it becomes clean-up such as creating a new field for the headers and then hiding the headers of the indexing etc.

           

          Steve

          • 2. Re: How can I add a column inside tableau and populate it
            Alex Martino

            Hi Steve thanks for responding. I took a look at your public visualization and I just saw this (bottom) for instructions.

             

            How can I see the row and column that you added into the data set? So you are saying the column should be for waterfall and the row should have all "Starts" and one row/column interesection of "end"? How did you add this to the data set? Manually?

             

            I'm confused about the null record row, as it's not null if you are adding "start" and "end" entries. For example, above you have "Region" showing as waterfall anchor but wouldn't that show up as "end" (or "start") if you have that text filled under the "Region" column.

             

            I've read your description a number of times but this is complicated haha. I sort of get it though. You are using missing date values and then telling it where to plot the measures. I just don't completely understand the row/column set up and how you add that to the data set. Thanks!!

             

            "This is the measures waterfall, to build it needs a custom waterfall field call it 'Waterfall' and at least one null record to be the anchor, set this field to 'Start' in your 'Waterfall' field and the remianing to 'End'

             

            Once in place, you can easily build the rest of the waterfall, the only caveat being that any filtering must include the 'Null' field (call this what you want in the source), without this, the anchoring disappears and your chart shall go up in smoke"

            • 3. Re: How can I add a column inside tableau and populate it
              Alex Martino

              Steve Martin I'm looking at your first row and it has things like unknown, -1, null, and 0. Does any of this stuff matter? Thanks.

              • 4. Re: How can I add a column inside tableau and populate it
                Alex Martino

                My measures are created (not part of the data set) and thus when I use them they only require aggregates. Should I just delete the sum part and keep the TOTAL?

                 

                It says "an aggregate function is already an aggregation and cannot be further aggregated"

                 

                THanks. Slowly but surely

                • 5. Re: How can I add a column inside tableau and populate it
                  Alex Martino

                  Hey!! I finally figured it out.

                   

                  Now, my final question is can you segment this information by dates? I have data from the beginning of the year and I want to only look at a certain month.

                   

                  Is this not possible since you are using data information to create the columns? Thanks.