7 Replies Latest reply on Aug 25, 2016 7:43 AM by Jamie Morgan

    Weekly Movement in new last column only

    Jamie Morgan

      Hi I am trying to find a way of getting the week on week % change of sales numbers but only want to see it for the latest week but also in a new column on the end. I have saw some old posts on how to do it but I think Tableau has changed so much since then that they don't seem to be relevant and I can't work out how to follow it with the changes. Also there is too much data to be able to do the dashboard workaround that seems to be one of the common suggestions.

       

      Any help is greatly appreciated

       

      Jamie

        • 1. Re: Weekly Movement in new last column only
          Carl Slifer

          Howdy Jamie,

           

          How are you trying to show this information? I'm going to assume returning a single value. This same concept would work for graphs but I'll use it as if it were a text table. IF you want a graphic instead of a text table also drag the measure to the rows shelf in the second step.

           

          1) The easiest way is to drag your date field to the columns shelf and change it to using the week option.

          2) After that Drag your measure to text.

          3) At this point you have a ton of columns and a single row. Never fret.

          4) Right click your measure and change it to using quick table calculation and choose % difference.

          5) Now make this calculated field

          LAST() = 0
          It's a single statement so it is a boolean. It will either return true of false based of the calculation LAST() - more on that later.

          6) Put this onto the filters shelf and set it to only allow true values.

           

          Tada!

           

          To learn more about the table calculation LAST() I used check out this post. Its quite informative. https://www.interworks.com/blog/tmccullough/2014/09/29/5-tableau-table-calculation-functions-you-need-know

           

          Cheers!

          Carl Slifer

          InterWorks

          2 of 2 people found this helpful
          • 2. Re: Weekly Movement in new last column only
            Jamie Morgan

            Thanks Carl,

             

            Do you know of a way to still show the original data for all the previous weeks at the same time and just tag this onto the end of a data table?

             

            Regards

            Jamie

            • 3. Re: Weekly Movement in new last column only
              Carl Slifer

              Hi Jamie,

               

              By original data you mean the actual weekly values? But you want the last week to show the actual and the % difference from the prior week?  If so do not use the LAST() = 0 Filter.

              Instead create your calculated field for the weekly change. Then create a calculation that says...

              IF LAST() = 0 THEN ^^^^^Insert the the table calculation from above into here. You can do this by creating the calc and dragging it into the calculated field window^^^^ END

              This will (only for the last field) return this % difference and for the others return null.

              Then also use your regular sum(sales) on the text shelf with it or on the rows shelf and put the above as a label.

               

               

              The inserted section from above will look something like this.

              (SUM([Measure])  - LOOKUP(SUM([Measure],-1) )

              /

              (ABS(LOOKUP(SUM([Measure],1))

               

              Am mobile so please excuse syntax error iphone is not forgiving.

               

              Cheers

              Carl Slifer

              InterWorks

              1 of 1 people found this helpful
              • 4. Re: Weekly Movement in new last column only
                Jamie Morgan

                Thanks Carl, I don't know if I'm doing something wrong or if its just not possible to get it the way I want it. I just seem to get a little lost following you and how to create this. I have included a sample file of how far I have got, I am using the superstore sample data as I cannot share my own data, and below I have an excel screenshot of how I would like it to look if it's possible. If only we could write our own formulas for grand totals that would be ideal as essentially I think that's where I would like to to be.

                 

                Regards

                Jamie

                 

                • 5. Re: Weekly Movement in new last column only
                  Carl Slifer

                  Hi Jaime,

                   

                  I'm out working with clients and then am attending a user group so my reply

                  will be tragically slow for you. However you sort of can make a separate

                  formula for grand totals. In this case it's really no needed I don't think.

                  I'm unable to open the workbook currently but a sneaky way to define a

                  formula for the totals is to say.

                  If First()=Last() then ....grand total formula....

                  Else .... Other formula.....

                  End

                   

                  However I don't Reccomend doing this unless you understand what each bit

                  does.

                   

                  Cheers!

                   

                  On Monday, 22 August 2016, Jamie Morgan <tableaucommunity@tableau.com>

                  • 6. Re: Weekly Movement in new last column only
                    Steve Taylor

                    Hello Jamie

                     

                    I think the easiest way to achieve what you’ve shown in the excel table would be to have separate weekly change calculations for each of your measures, then display them in a separate worksheet to your yearly totals and place the two worksheets adjacent to each other in a dashboard like so:

                     

                     

                    The Week on Week calculation takes the SUM() of a measure for the latest 7 days in the datasource and subtracts the SUM() of a measure for the 7 previous days then divides by the previous 7 day’s figure. That’s what this calculation does, note that if you wanted to look at complete weeks you’d need to use DATETRUNC() to get the beginning of the current latest week and adjust the calculation to return values within 7 and 14 days of that “beginning of latest week” date.

                     

                     

                    Please see the attached workbook for a demo of the latest 7 days method, please get in touch if this isn’t the solution you were looking for.

                     

                    Thanks

                    Steve Taylor

                    • 7. Re: Weekly Movement in new last column only
                      Jamie Morgan

                      Thanks Steve

                       

                      I have tried to avoid the dashboard workaround as it doesn't allow scrolling down long sheets of data.

                       

                      I don't know if I would be able to combine your answer with Carl's for the grand total to give the output that I'm looking for?

                       

                      Regards

                      Jamie