6 Replies Latest reply on Dec 2, 2016 1:42 PM by Jonathan Basirico

    Aggregating table calcs -- how to?

    Jonathan Basirico

      Hello,

       

      I am trying to count the number of times that a table calc yields a given result. In the attached workbook, I have stock ticker data from Google Finance, for which I created a table calc to compare the ticker for one day to the previous day I would like to be able to do some analysis on trends, for example the number of times an increase is followed by another increase over a certain date range. Below, you can see how the table calc looks by date, but I am trying to figure out how to aggregate it -- any suggestions?

       

      Thanks, Jonathan

      Untitled.gif

        • 1. Re: Aggregating table calcs -- how to?
          Joe Oppelt

          I can help you with that.


          But upload a twbX file.  Not a twb.  (Do FILE -->  "Extract Packaged Workbook" to get a twbX.)

           

          And specify your tableau version so I can work in the same version that you are.

          • 2. Re: Aggregating table calcs -- how to?
            Jonathan Basirico

            Here is the twbx in version 10.1 desktop version.

            • 3. Re: Aggregating table calcs -- how to?
              Joe Oppelt

              Hah.  Turned out to be a 9.3 workbook.  (I have all the versions though.)

               

              See attached.

               

              I used [Calculation 2] to count up how many successive duplicates you have in a row.

               

              For kicks, I made a copy of your Trend calc, and used that to color the new field differently.

              • 4. Re: Aggregating table calcs -- how to?
                Jonathan Basirico

                Joe, thanks for your help. Sorry about any confusion with the Tableau version as I have different versions depending on which computer I am using (not optimal). Saving as 9.3 was the right idea.

                 

                I wasn't previously familiar with the PREVIOUS_VALUE function, so that is good to know. However, I don't think you've captured what I'm looking for, although it might be part of the solution. What I am trying to do is analyze characteristics of each trend. For example, as a group, what are characteristics of days by trend (e.g., for days with trend "increasing", what is the average daily change, total count of days) without being presented on a timeline.

                • 5. Re: Aggregating table calcs -- how to?
                  Joe Oppelt

                  I just presented it on a timeline to do something with that calc.

                   

                  You can do stuff with that trend calc.

                   

                  So in Sheet 5 I just made a copy of the Ticker sheet, but I took AVG([Metric Value] off the column shelf.  Just shows that you don't have to have the actual measure you're table calc-ing on the view.

                   

                  In sheet 6 I changed things around so that we can separate the various trends and measures and look at them separately.  And I created a moving average calc (just to do something for demonstration purposes.)

                   

                  I also created three max moving average calcs.  One to get the max for each little line, and one to get the max for the whole chart.  (Not that these mean anything useful.  It's just for demonstration purposes.)


                  And you'll see that the three MAX calcs are identical in the calc logic.  The difference is the way I edited them.  The first one (per measure) just does table across.  It gives you the biggest value for each measure.  (So for the Euro measure, the biggest peak across all the Trends is 29.03.)  The one for entire sheet does table across then down.  It gives the biggest peak from among all the cells, and it's the same number in the tooltip, no matter what cell you are hovering on.

                   

                  The third does it per cell.  Look carefully at the pill for this calc.  Right click and select "Edit Table Calc".For the calc itself I have TABLE(across).  But you'll see a pulldown at the top of the edit box with the field name for this calc. Because this calc is built on another calc, we can set the various table calcs individually.  For The Moving Average calc I still have TABLE(across).  But for the MAX for CELL calc I have selected CELL.  So the moving average is calculated across the line, but we are "chopping off" the calculation to evaluate for each cell instead of across the entire measure.

                   

                  And then I did one more thing.  I found the date of the Max Moving average per cell.  Check out that calc.  You can put IF logic inside a table calc.  This calc finds the date of the max value in that cell (and internally all other dates are null) and then the WINDOW_MAX grabs the max along the line and sets that value for all points on the line.

                   

                  I don't fully have my head wrapped around exactly what you are looking to do, but I hope this opens the door to showing you ideas of how you can manipulate (and re-manipulate) calcs and table calcs.

                   

                  But even this hacked-together viz shows you the volatility of the market when the recession hit in late 2008.

                  1 of 1 people found this helpful
                  • 6. Re: Aggregating table calcs -- how to?
                    Jonathan Basirico

                    Thanks, I think you have opened the door sufficiently .

                     

                    Mostly, the point of this exercise was just to see what I could do with a web data connector I found for Google Finance, perhaps see if I could get Tableau to provide buy/sell indicators for a portfolio based on some measure. In this example, the indicator is the trend (e.g., "increasing") and the stock would be picked (ranked) based on the highest likelihood of a trend longer than a certain threshold (e.g., 2 days). In this version, I only included three stock tickers just to try it out.

                     

                    I added another tab "trend holding length v2" to see how this might work.

                     

                    Thanks, Jonathan