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

    Help with % Change over Time

    Mine Safety
    Mine Safety Sep 22, 2017 11:05 AM

      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

      • 343 Views
      • Categories: Calculations, Dates & Times
      • Tags:
        • 1. Re: Help with % Change over Time
          Ivan Young
          Ivan Young Sep 22, 2017 1:16 PM (in response to Mine Safety)

          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
            Mine Safety Sep 22, 2017 1:30 PM (in response to Ivan Young)

            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
              Ivan Young Sep 22, 2017 2:13 PM (in response to Mine Safety)

              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
                Mine Safety Sep 22, 2017 4:50 PM (in response to Ivan Young)

                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
                  Mine Safety Sep 24, 2017 7:48 AM (in response to 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.