11 Replies Latest reply on Sep 28, 2016 12:02 PM by Joe Oppelt

    Table Calculations

    Galen Flynn

      Hello,


      I am creating a table in Tableau. I have the data setup like the following with week numbers in rows:

       

                          Group 1                                   Group 2

                FY14     FY15     FY16              FY14     FY15     FY16

      1

      2

      3

      4

      5

      6

      7

      8

      9

       

      I am trying to do two things;

       

      1. On the far right side have a 'grand total' for each year per week.

      2. At the bottom I am looking to have a %YOY change for the total for each year.

       

      Any ideas on how I can do this? Thank you for any help!

       

                          Group 1                                   Group 2                                             Grand Total

                FY14     FY15     FY16              FY14     FY15     FY16          FY14TOTAL     FY15TOTAL     FY16TOTAL

      1

      2

      3

      4

      5

      6

      7

      8

      9

           x%     y%                                   z%     -k%    

        • 1. Re: Table Calculations
          Joe Oppelt

          Consider posting a sample workbook.  (Let me know the version of Tableau you are on.)

           

          You can do what you are looking to do.  You might have to split it into more than one sheet and position the parts on a dashboard.

           

          I can help you get that to work.

          • 2. Re: Table Calculations
            Galen Flynn

            Hi Joe,

             

            Thank you very much for your help.

             

            I've attached a sample workbook. I am working on Tableau Desktop 9.3.

            Of course I had to mask some information so this is just a sample.

             

            Thank you!

            • 3. Re: Table Calculations
              Galen Flynn

              Attached a non-packaged workbook if that's what you need

              • 4. Re: Table Calculations
                Joe Oppelt

                See attached.

                 

                Sheet 2 is a copy of Sheet 1.  I added a table calc to add the weekly cells across the groups for each year.  Click the triangle of [Annual total ...] on the text shelf.  Do Edit table calc.  You will see that I selected Processing Group for Compute Using.  That tells it to run along the Processing Group and add up the numbers.

                 

                Initially I had used ADVANCED in "Compute Using", and set a "Level" and a "Restart".  You will see that in the subsequent sheets.  But when I was done, I realized I could have just taken the easier route and simply select [Processing Group] for "Compute using".  These are equivalent, as it turns out.  Since I had originally done all the work with the more complex setting, I left it that way in all the other sheets, but you can just use [Processing Group] when you do this in your own workbook.

                 

                So add up all the FY2014 values for week 1, and you'll get 4346.  Now, I know you don't want it to look this way.  See Sheet 3.  This is a copy of Sheet 2, except I moved [processing group] to the detail shelf.  For the sake of demonstration, pull the filter [index] off the filter shelf.  You will see that you actually get the value plus a series of dots.  What's happening there is that you are getting 8 copies of each value because there are 8 groups.  Hit back-arrow to put the filter back on.  The [index] calc is there to capture the index value for each cell.  Click the triangle on [index] in the filter shelf.  Edit Table Calc.  You will see that I am running this along [processing group].  Click OK.  right click on index and do edit filter.  You will see that I am selecting only the value of 1.  THis tells tableau just to display the first of each of those 8 copies.

                 

                Now you have a year-by-year total for each week.

                 

                See Sheet 4.  It's a copy of Sheet 3.  I added another table calc to add up the values running down each year of the total sheet.

                 

                See Sheet 5.  I added [index] AGAIN to the filter shelf.  the second one tells tableau just to get the last [Annual total of weeks] value running down the table.  But then I realized that you wanted to do the annual down the table by group.  (I think.)  So see  Sheet 7.

                 

                This is a copy of Sheet 1.  I made another table calc and told it to add the initial SUMs down the sheet.

                 

                See Sheet 8.  Same game with index.  Just show one value.

                 

                See Sheet 9.  I created another calc to get the YOY change of the totals from Sheet 8.  (I forgot to hide the header for WEEK on that one.  You don't want to leave that on there.  It will confuse your users.)

                 

                See Dashboard 1.  Here we have a problem.

                 

                I created a container and put Sheet 1 and Sheet 3 side-by-side.  Because both sheets are scrolling sheets, I would expect you would want them to scroll together with one drag.  We don't have the concept of a scrolling container, which would take care of that for you.  So I made the dashboard really long, and the container really long, and then you can scroll the whole dashboard.  Problem is, the headers scroll off the top when you do that.  This is a limitation I don't know how to work around.

                 

                You can artistically place the YOY sheet 9 at the bottom of all this.

                 

                I could get you the numbers.  Now it's a question of how you want to display this stuff.

                • 5. Re: Table Calculations
                  Joe Oppelt

                  PS:  Packaged workbook is the only way to share workbooks on here.

                  • 6. Re: Table Calculations
                    Galen Flynn

                    Wow Joe this is amazing!

                     

                    Thank you very much for your help!

                    • 7. Re: Table Calculations
                      Joe Oppelt

                      When you incorporate this into your actual workbook, if you run into problems, ping back.  I'll get an email with your update and get back to you.

                      • 8. Re: Table Calculations
                        Galen Flynn

                        Hi Joe,

                         

                        Thank you for all your help so far! I think I just about it got it -but I am running into one problem. For the year-over-year calculation, this is calculating the % change for the total for each year.

                         

                        I'm really looking to calculate the % change YTD. So if we are on week 13 then the % change for the total up until week 13 for all years. If that makes sense.

                         

                        As always, any help is appreciated! Thank you

                        • 9. Re: Table Calculations
                          Joe Oppelt

                          I can't remember this one.


                          Which sheet are you looking at in there?  And define for me what you mean by % change for the total (what total?).  And will you need to let the user select a week to make the comparison on?  Or will it always be week 13?

                          • 10. Re: Table Calculations
                            Galen Flynn

                            Oh sorry!

                             

                            Looking at the workbook you attached (Tableau_Calculation_Test B) [Sheet 9].

                            You created a YOY calculation which calculated the percent difference from the previous fiscal year.

                             

                            I made a few modifications so I could basically see an overall %YOY from year to year (One % for all Process Groups).

                            What I am looking to do is calculate YTD YOY.

                            Not looking for users to choose the current week but instead derive the most current week number.

                             

                            So if we are currently on week 13 I'm looking to calculate YOY for

                            total for 2016(week 1 - week 13)

                            total for 2015(week 1 - week 13)

                            total for 2014(week 1 - week 13) etc

                             

                            Next week will of course be week 14.

                            • 11. Re: Table Calculations
                              Joe Oppelt

                              Galen -- See attached.

                               

                              Sheet 10 is a dup of sheet 9, with the filter removed so we see all the rows.

                               

                              I created two calcs so that we can keep track of which week we want to be concerned about.  [Week Today] tells us what fiscal week we're in.  I was hoping Tableau would let me set a FISCAL START for this one so that it would automatically translate today's week value of 40 into a fiscal week starting in July.  No dice, so I just had to do it myself.  See the code in that calc to see how I am shifting the week around.

                               

                              Then I created a [Fiscal Week] calc that will sit on each row.  You can see that I did the same manipulations in there as I did in [Week Today].

                               

                              I displayed them on TEXT in sheet 10 so you can see how they work.

                               

                              Sheet 11 is a duplicate of Sheet 10.

                               

                              I created a new [Annual Total ...] calc that sums only up to the current week.  Displayed that on Sheet 11 in place of the old [Annual Total...] calc.  And I did the same for a new YOY calc.


                              And there you have it.  It will always trigger off TODAY() so it will add another week next week.