7 Replies Latest reply on Dec 11, 2017 10:11 AM by vicky xue

    Calculate percentage difference week over week

    vicky xue

      Hi Tableau gurus,

       

      I want to calculate the percentage difference between the previous two weeks (10/12/17-10/26/17) and the current two weeks (10/26/17-11/09/17). For example, how many difference percentage of Type 1 Error resides in Channel -11? Appreciate any tips/advise.

       

      Attached workbook fyi. Thanks.

       

        • 1. Re: Calculate percentage difference week over week
          Joe Oppelt

          Oops.  You uploaded a TWB file, but others cannot open a TWB.  We need a TWBX file, which will include data.  You get that doing FILE-> Extract Packaged Workbook.

           

          But let me point you to the WINDOW_SUM function.  It will look across the whole table, or look at the offsets you tell it to do.

           

          So WINDOW_SUM(SUM(sales), -1, 0) will add up the prior week and the current week, and WINDOW_SUM(SUM(sales), -3, -2) will add up the two weeks before that.  (Assuming your table is partitioned in weeks.)

          • 2. Re: Calculate percentage difference week over week
            vicky xue

            Thank you for the instruction, Joe. Here attached TWBX file.

            • 3. Re: Calculate percentage difference week over week
              Joe Oppelt

              Ooh.  Yours is a challenging example.  We have to play with table calcs, and with the settings of proper addressing so that Tableau walks the table in the order you need it to evaluate.

               

              In the attached, on sheet Test1, I created a table calc called [Total DRs by Weeks].  I added it to the details shelf , and I put it in tooltips so you can see the values that are calculated.  (You'll need to do parallel treatments for [#DRs] and [Seconds].  I just did this for DRs.)

               

              So if you scroll over, for example, Channel 2, the first weekly group, blue bars, you'll see that for FA1 there are no DRs, and for FA2 the blue DR bar is 9.  ANd for the second weekly group FA1 has DR = 5, and FA2 has DR = 13.  So the calc I placed there shows a total of 9 for the first week, and 18 for the second week.

               

              Click on the triangle for the [Total DRs by Weeks] pill on the marks for SUM(Seconds).  Select "Edit Table Calc"

               

               

              You'll get this pop-up box:

               

               

              Notice the settings for "Compute Using".  I had to reorder the dimensions in the "Specific Dimensions" list so that they get evaluated in a specific order, and then I have it restart for each week.  (So it also restarts for all the dimensions above it.)  (You reorder the dimensions by click-dragging them to the order you want.)  That's how I got it to give me a value by week-group, by event type, and by Network.

               

              BTW, it's convenient that you have grouped these 2-week chunks like this.  Now we don't have to mess with all sorts of offsets to look back and grab 2-week chunks.  We just have to look back -1 to get the previous chunk of two weeks.  And right now apparently you only have 2 chunks of weeks in your data, but when you add more, the existing calcs I have created will continue to work as I designed here.

               

              So I made a copy of your Test1 sheet.  (I actually didn't have to, but it's what I do when I explain stuff that I'm doing in my solution, especially when I change existing settings to achieve the next step.  In this case that didn't actually happen.)

               

              I made a new table calc that gives me the difference between the current weeks and the prior weeks.  Look at [Diff DRs by week].  Lookup() lets me look backward (or forward!).  Here I want to take the prior value of [Total DRs...] and subtract it from the current.  Edit the table calc settings for this one like we did on the prior sheet.

               

               

              There are two important things to point out here.  First, I need the [Diff...] calc to run in a certain order -- along networks and weeks, and to restart every week.  But also, this calc references the other table calc, and I need to make sure that for this calc, the [Total ... ] calc walks the table correctly.  I have circled the pulldown arrow to show how you get to the settings for the [Total...] table calc as it gets evaluated in the context of the [Diff...] calc.  You need to make sure both calcs are set properly.  Sometimes Tableau picks up the settings for the referenced calcs, but don't count on it.  Make sure of it by double checking here.

               

              So now look at the red bars for DRs for Channel 1, for example.  There are no red bars for the first chunk.  There are a total of 4 DRs for the second weeks (3 + 1).  (You'll see that in tooltips.)  As I built this, I noticed that originally I was not getting a DIFF value on the second weeks.  That's because there was NULL values for the first weeks.  So I added the ZN() function in the DIFF calc.  That says to insert zero if NULL is found.  Now I get a diff of 4 between the two weeks.  Look at blue bars for Channel 11.  8 - 3 = 5.

               

              You can use the same principle to calc %DIFF or any other analysis.

               

              Notice also that you don't get a DIFF value for the first chunk of weeks for any Type or Network.  That's because there is no value for offset -1 because of the RESTART EVERY WEEK setting.  You don't want to be pulling the last value for the prior Network or Event, etc.

               

              So that was a long explanation.  Digest this and come back with questions.  I'll  be glad to explain things further if needed.

              • 4. Re: Calculate percentage difference week over week
                Tim Dines

                I think you will be able to get what you need from this thread.

                 

                Adding week over week % change by week and category

                • 5. Re: Calculate percentage difference week over week
                  vicky xue

                  Thank you so much for the very informative answer. That is very helpful.

                   

                  Question: Could thThank you so much for the very informative answer. That is very helpful. Question: Could the table calculation below achieve the same calculation result as yours? e table calculation below achieve the same calculation result as yours?

                   

                  • 6. Re: Calculate percentage difference week over week
                    Joe Oppelt

                    That is giving you the diff between the two blue (or red, or orange...) bars within a given [Fatal] for a given [Channel].

                     

                    if that's what you are looking for, it's doing what you want.

                     

                    I looked back at my earlier workbook that I uploaded.  I hadn't attached the last version I created when I was building the example.  I was also taking it a step further, giving you a diff for a given channel, summed along all [Fatal]s .  If you are not looking for that cut of data, then you have what you need with the example you just uploaded.

                    • 7. Re: Calculate percentage difference week over week
                      vicky xue

                      Heartfelt thanks to you, Joe.