5 Replies Latest reply on Nov 11, 2018 9:23 AM by Paul Wachtler

    Create "CHANGE" and "%CHANGE" Columns

    pankaj jain

      Hello

       

      I have a Stock Market data set in Excel having the EOD "End of Day" Stocks Data for various Symbols. There is one Row for Each Symbol for Every Trading Session. This data set has got multiple columns, out of that, there are three columns named -

       

      close_price

      total_trade_quantity

      total_trade_value

       

      for which I need to Calculate the "CHANGE" and "% CHANGE " Values in the form of NEW COLUMNS, so that I am able to use these newly calculated columns inside any visualizations in Tableau. The "CHANGE" is calculated as current days value - previous day value. The "% CHANGE " is also calculated in the similar manner by using the current day value and the previous days value of the respective stock.

       

      So basically, we will get these 6 new additional columns added to the data set, named -

       

      close_price_CHANGE

      total_trade_quantity_CHANGE

      total_trade_value_CHANGE

       

      AND

       

      close_price_%CHANGE

      total_trade_quantity_%CHANGE

      total_trade_value_%CHANGE

       

      Can someone please tell how to best do this task, so that the new columns data become available for any other visualizations within Tableau. That is my main motive. If I cannot use these new columns data for other visualizations, then that will not solve my problem.

       

      Thanks a lot.

      x CHANGE.png

       

       

      Software Versions being used -

       

      Tableau Desktop Professional 2018.2.3 64-bit

      Windows 7 Ultimate 64-bit

      Microsoft SQL Server Management Studio version -12.0.2000.8

      Microsoft Office Professional Plus 2016 64-bit

       

      I have attached the Packaged Workbook, which has the above mentioned excel data in it. I have not yet created any visualizations it it, because first the above mentioned six new columns data needs to be created somehow.

        • 1. Re: Create "CHANGE" and "%CHANGE" Columns
          Paul Wachtler

          Hi Pankaj,

           

          If you just needed these fields for a single view, I'd be suggesting Table calcs.  However, those don't easily transfer to other workbooks, unless you have the same level of detail included.

           

          Anyway, the solution you're looking for involves taking a copy of your dataset (lets call this #2), joining it to the original (#1), and using a join calculation that looks like this:

          Day from Table #1 = datediff('day',Day from Table #2,1). 

           

          This will join the copied dataset to your original on the previous day being equal to the current day.  That way you'll have two sets of Close Price, Total Trade Quantity, and Total Trade Value in each row - one set for the current day and one for the previous. 

           

          Then you'd simply create calculated fields for each of your change measures like this:

          Current Day Value - Previous Day Value

           

          and

           

          (Current Day Value - Previous Day Value)/ Previous Day Value.

           

           

          If you can attach the excel file you're using then I can mock it up.

           

          Best,

          Paul

          • 2. Re: Create "CHANGE" and "%CHANGE" Columns
            pankaj jain

            Thanks Paul.

             

            I think this could be done in much easier way, without the need to create any additional data sets etc.

             

            All we are trying to accomplish is a very simple calculation of Change and % Change only. I think there would be some formula in Tableau for doing such calculation, I just don't know where to find it.

             

            If someone has any experience in working with STOCK MARKET DATA, can you please guide in this ?

            • 3. Re: Create "CHANGE" and "%CHANGE" Columns
              Paul Wachtler

              Hi Pankaj,

               

              The issue you're going to have is that you can set up these calcs on one worksheet with table calcs and it'll work but when you go to another worksheet you'll have to reconfigure your table calcs.

               

              Table calcs work based on the fields in your view so if you have different fields on different worksheets you're going to have to reconfigure how they calculate each time.

               

              If you want to get this to work on one view, I can help with that. If you want to use table calcs for each view, that's fine but it's a lot of work.

               

              The solution I offered will let you use the same calculation on any new page without having to reconfigure anything.

               

              Paul

              • 4. Re: Create "CHANGE" and "%CHANGE" Columns
                pankaj jain

                Thanks again Paul.

                 

                What I understand from your reply is that we cannot use the method of "TABLE CALCULATIONS" in this, because that works only for the worksheet in which they have been calculated. So if we want to use these same new values on other worksheet, we will have to repeat the same process again, right ?

                 

                How about using the method of "Calculated Field" for doing this work ? Does that also suffer from the above issue, even if we do this calculation in the DATA SOURCE Tab itself ? I guess that new column would work on all the worksheets.

                 

                Are there any other possible ways for doing this ? I would really like to know what other Tableau Experts have to say on this thing. Perhaps someone would look at it from different perspective and might suggest some easier solution. Because getting the values for CHANGE and %CHANGE is such a basic requirement in so many different user scenarios, I was hoping there would be some very simple solution available for this for sure. I maybe wrong !


                Regards

                • 5. Re: Create "CHANGE" and "%CHANGE" Columns
                  Paul Wachtler

                  Pankaj, calculated fields are simply any custom field that you create with a calculation. Table calcs are implemented generally with calculated fields.

                   

                  You're more than welcome to a second opinion, but you either can use Table calcs and update them on each view, or we can duplicate your datasource to get what you asked for.

                   

                  If no one else has a simpler solution, which I don't expect, let me know if you want help.

                   

                  Paul