5 Replies Latest reply on Sep 24, 2017 7:48 AM by Mine Safety

    Help with % Change over Time

    Mine Safety

      I am trying to structure a text table based on stock portfolios belonging to specific individuals. The table contains measure values for:

       

      • an individual stock's total % of a Portfolio
      • # of shares owned
      • price paid for shares
      • Value of stock holdings
      • % change in shares owned from previous quarter

       

      But when looking at the "% change in shares owned from previous quarter", it doesn't seem to be correct. I can't quite figure out why, but the number is much larger than I would expect it to be. I am fairly certain it should be computing using "Table (down)". Any ideas as to what I'm doing wrong?

       

      2.png

        • 1. Re: Help with % Change over Time
          Ivan Young

          You don't want table down, that will just calculate the change from the previous row, you need it at the level of quarter restarting every investor.  You will also need to change your date quarter filter to a table calc, currently you are filtering out all q1 data so you can't do a qoq comparison.  The formula to change the date to a table calc is LOOKUP(ATTR([Date]),0). 

          • 2. Re: Help with % Change over Time
            Mine Safety

            Sorry, I'm not sure I fully follow. My apologies as I'm new to Tableau.

             

            How would I got about accomplishing at the level of quarter restarting every investor?

             

            And per your point about creating a table calc... Would that just look like this:

             

            Screen Shot 2017-09-22 at 4.29.39 PM.png

            • 3. Re: Help with % Change over Time
              Ivan Young

              First you should create your Date Filter just like you have above.  You need make the filter discrete (blue colored), which doesn't seem to be an option when it is on the filters shelf.  What I did to make this work is to first place Date Filter on rows then move it to filters.  After you have the discrete Date Filter on filters select 4/1/2017.

               

              Now click on Quarterly % Change and select edit table calculation.  Set the compute to Specific Dimensions and set like the screenshot below.  This should will give you the qoq change.  Give it a try and let me know if you have any questions.

               

              • 4. Re: Help with % Change over Time
                Mine Safety

                Thanks, this is very helpful and does seem to work for the most part, although 2017 Q2 seems off for some entries. For example, the below values are wrong:

                 

                Screen Shot 2017-09-22 at 7.47.30 PM.png

                 

                I thought I read somewhere that it's best practice to change the way table calculation is being computed for all measures. Is that accurate?

                 

                I also have a few more formatting questions if you don't mind.

                 

                • Is there a way to display the filter as Q2 2017 anymore, rather than the specific date it correlates to?
                • I'm trying to make negative changes red, positive changes green, and no change black via the Color mark. Beforehand I was able to do this quite easily using the Custom Diverging palette.

                Screen Shot 2017-09-22 at 7.15.04 PM.png

                But now it just seems to alternate?

                Screen Shot 2017-09-22 at 7.15.25 PM.png

                • How can I make it so that only the % Change Quarterly column changes color rather than the whole row? This was possible previously, so I'm unsure what changes made it no longer possible.
                • 5. Re: Help with % Change over Time
                  Mine Safety

                  I was able to resolve these on my own for the most part. The % change = 100% of portfolio for new entries was resolved by adjusting the table calc to:Screen Shot 2017-09-24 at 10.43.00 AM.png

                   

                  And I was able to use Legends per Measure to color only that specific column. Only other thing I'm still stuck on is changing the filter to display the quarter rather than exact date.