1 2 Previous Next 16 Replies Latest reply on Jul 26, 2016 8:33 AM by Michael Moore

    % change from 1, 5, 10 days ago

    Michael Moore

      I have a data source of trading data measures, organized per trading day.

       

      I want to calculate a %change column for each date, showing how the market price has changed over last 1, 5, and 10 trading days.

       

      How can i do this right in the data tab, and create a new measures column per each %change?  I tried this: ((lookup( sum([Price]),-5) - sum([Price]))/LOOKUP( sum([Price]),-5))/LOOKUP( sum([StDEV5]),-5) . However this doesnt go back 5 cells in the data, but rather 5 cells within the partitioning of a table -- not what i need. I need a new measures column in the source data that goes 5 cells back. This is easy to do in excel, but i'd rather not crutch on VBA if possible.

        • 1. Re: % change from 1, 5, 10 days ago
          Chris Dickson

          Hi Michael,

           

          are you able to supply a workbook for us to look at, Lookup is a table calculation, so you can tell it against what to calculate by right clocking on the pill and 'Edit Table Calculation'

           

          Screenshot 2016-07-26 10.21.02.png

          1 of 1 people found this helpful
          • 2. Re: % change from 1, 5, 10 days ago
            khalid norat

            Hi Michael,

             

            I'm not sure if a table calculation will be most effective for your requirement.

             

            If you are looking for  a field that gives you %change 1 , 5 or 10 days ago there are other ways of achieving it using parameters and calculated fields.

             

            If you can share a sample workbook I can demonstrate how this can be achieved

            1 of 1 people found this helpful
            • 3. Re: % change from 1, 5, 10 days ago
              Ashish Chaudhari

              Hi Michael,

               

              can you please attach some dummy data on which I can take this forward?

               

              -Ashish

              • 4. Re: % change from 1, 5, 10 days ago
                Michael Moore

                This is my first time in the forum. I apologize if I am not up to protocol. My workbook contains 9 sheets and 60 measures. I can create a sample book if needed, but imagine it is a simple yahoo finance file of two columns: 1)datetime and 2)closing price.

                 

                How do I create 3 more columns of 1 day % change in price, 5 day change, and 10 day change up to that date?

                • 5. Re: % change from 1, 5, 10 days ago
                  Michael Moore

                  i need the new % change calculated per datetime though, the table calculations dont seem to be the answer. how do i do this down to the individual data point level? I want to do the calculation before any aggregation, not afterwards. I need to do the % change from 5 cells (5 days) previous, all i see are partitions in the table calculation.

                  • 6. Re: % change from 1, 5, 10 days ago
                    Michael Moore

                    My current workable solution to calculate a new % change of price from 5 cells previous column, is to use an excel macro before loading into tableau.

                    • 7. Re: % change from 1, 5, 10 days ago
                      Ashish Chaudhari

                      This is enough information. Working on this. I will get back to you soon.

                       

                      -Ashish

                      • 8. Re: % change from 1, 5, 10 days ago
                        Ashish Chaudhari

                        Hi Michael,

                         

                        Please find the attached screenshot and confirm the output. As Required I have added 3 additional columns which are giving % change 1D, 5D and 10D back. Attached is the tableau 9.3 version.

                         

                        This table calc is added for the 1D. Refer to the screenshot.

                        5Days_back

                        (SUM([Price]) - LOOKUP(sum([Price]),-5))/LOOKUP(sum([Price]),-5)

                        10Days back

                        (SUM([Price]) - LOOKUP(sum([Price]),-10))

                        /LOOKUP(sum([Price]),-10)

                         

                        I hope this one helps. Let me know if this solves your query.

                         

                        Thanks and Regards,

                        Ashish Chaudhari

                        3 of 3 people found this helpful
                        • 9. Re: % change from 1, 5, 10 days ago
                          Michael Moore

                          Table calculations do not work for my needs. I also been able to create a table calculation such as the one you have created. This is not what I need.

                           

                          I need to create a new measures column in the source data tab. I can add or subtract other columns within the same row to create a new column in that tab.

                           

                          However, as far as i know, there is no way to create a new measure column using X number previous cells within the same column. This is simple in excel. Is there a solution within Tableau, or do i need to do this in excel before loading into tableau?

                          • 10. Re: % change from 1, 5, 10 days ago
                            Shinichiro Murakami

                            Michael,

                             

                            Still uncertain what you want.   You can attach excel workbook with your formula instead, to show us what you exactly need.

                             

                            thnaks,

                            Shin

                            • 11. Re: % change from 1, 5, 10 days ago
                              Michael Moore

                              Ashish's workbook has the same formulas and same progress that i have.

                               

                              Table calculations are NOT what i need though.

                               

                              The problem: How do i create a NEW CALCULATED FIELD in the DATA TAB comparing the % change of price from 5 rows previous, to this row?

                               

                              This must be done in the data tab. Table calculations do not support my needs. I need a new calculated field column (in fact i need to calculate about 24 new columns) that i can use like any other measure calculated in the data tab.

                               

                              As shown, i can calculate the values in a table, but i can not use those values like measures.

                              • 12. Re: % change from 1, 5, 10 days ago
                                khalid norat

                                Hi Michael,

                                 

                                Just a quick question

                                 

                                How do you want to visualise this data.

                                 

                                Are you going to be showing only one days data with the % change for that date -1, -5 and -10 days respectively?

                                 

                                If you are not going to be showing a table  of multiple dates then I s will not work for you.

                                 

                                If you can mock up something in excel or paint or tableau of how you would like the data to be visualised. It would help in understanding what you are trying to achieve and assist us in helping you with the best solution for your scenario

                                2 of 2 people found this helpful
                                • 13. Re: % change from 1, 5, 10 days ago
                                  Michael Moore

                                  I am analyzing. I dont have a specific visualization in mind. I want to look at everything.

                                   

                                  I have about 40 different measures per day (and 12 years of data in 3k rows). In addition I have 1daychange (previous to current) and 1dayresults (current to current+1) to equal almost 125 columns.

                                   

                                  I am looking for patterns between what has happened previously ( what i call change), current values of measures, and what will happen (what i call results)-- for all 40 measures. FYI, the 40 measures are all options relationships for the same underlying product symbol.

                                   

                                  I would like to add change and results measures for more than 1 day (1 day is all my file has now) for more than 1 measure. Can I not create this new calculated field in the data source tab directly?

                                   

                                  Seems this may not be possible in tableau, and excel is the right tool for this task.

                                  • 14. Re: % change from 1, 5, 10 days ago
                                    khalid norat

                                    I agree it would be easier to achieve in excel.

                                     

                                    another way of achieving this would be to add multiples of the same data source and linking on date = date - 1 days

                                    you could either have the -1 day change by parameter or have multiple links to same data to have -5 days and - 10 days.

                                     

                                    but this wouldn't be the best use of tableau and you would most definitely have a performance impact

                                     

                                    You could quickly achieve the required data set in excel a lot quicker and efficiently before pulling data into tableau to visualise

                                    3 of 3 people found this helpful
                                    1 2 Previous Next