8 Replies Latest reply on Jan 17, 2017 7:26 AM by Chris C

    Value from Previous Date

    Chris C

      Background:

      In a dashboard, I have a line chart of sales broken out by weeks.  Clicking on the line will open below a more detailed list of the included categories and their respective sales.

       

      Question:

      How can I include a column next to the Sum(Sales) that will show Percent Difference from the previous week? 

       

      Problem:

      Using table calculations is useless when there is only one table (one week) to base the calculation off of.  I am not sure how to pull the week's prior data.  Even if I do, there is the issue of calculating the percent difference since the formula:

       

      (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

       

      is still a table calculation.

       

      Intended goal:

      To display this below the line chart

       

      I am using Tableau Desktop version 10.1

        • 1. Re: Value from Previous Date
          Jim Dehner

          Chris there was a similar discussion a couple days ago - search the title below and see if it helps

           

          Rolling 12 count even from the first month.

          • 2. Re: Value from Previous Date
            Shinichiro Murakami

            Hi Chris

             

            This is not very fancy way, but one proposal.

            Duplicate  data source and link with shifted date.

             

            Create field for week base date on current data source.

             

            [Order Date Week]

            datetrunc('week',[Order Date])

             

            Then duplicate data source

             

            On second Data source, shifted date of week.

             

            [Order Date Week]

            datetrunc('week',dateadd('week',1,[Order Date]))

             

            Link the fields.

            You can not link "individual date" because there might be missing date in shifted week.

             

             

            On second Data source, rename "sales" to "sales last period"

             

            On primary Data srouce

             

            [Sales Delta %]

            (zn(sum([Sales]))-zn(SUM([Sample - Superstore (copy)].[Sales-last period])))/zn(SUM([Sample - Superstore (copy)].[Sales-last period]))

             

             

             

            Thanks,

            Shin

            • 3. Re: Value from Previous Date
              Chris C

              Hi Shinichiro,

               

              I went in to the workbook to verify the calculations, but the Sales-last period values are hit and miss.  It either pulls the correct value (circled in green), wrong value (circled in red), or does not at all (typed in blue).

               

               

              I'm trying to understand the formula to see where the problem is.  My first confusion is the Order Date Week in the secondary data source.

               

              datetrunc('week',dateadd('week',1,[Order Date]))

               

              If 1 week is added to the date, how is it pulling the week prior?  I would imagine the DATEADD function to use -1.  But apparently it works out correctly, as seen in the below screenshot:

               

              • 4. Re: Value from Previous Date
                Shinichiro Murakami

                Chris,

                Thank you for th poting and sorry for the confusion.

                 

                Here is a background.

                 

                For + or - of "dateadd"

                 

                And we need to add 1 not minus 1

                 

                 

                Then the issue exist in different arena.

                 

                 

                 

                So combination of week x Region create situation 6/15 has null but 6/8 has data (pink).

                 

                This is one of the negative side of this method.

                Only work we don't need to filter region.

                 

                Then, I don't have good idea of alternative method....

                 

                Thanks,

                Shin

                1 of 1 people found this helpful
                • 5. Re: Value from Previous Date
                  Chris C

                  Thank you Shinichiro.  This is a clever idea.

                  • 6. Re: Value from Previous Date
                    Chris C

                    Hi Shinichiro.  This idea was working for me until I needed to publish the data.  Once I did, Tableau gave me an error message.  It couldn't copy the extract because of a "Data error (cyclic redundancy check)".  The definition online says this checks to see if there data that does not provide additional information, meaning I copied the same dataset with the same fields and Tableau does not like that. 

                     

                    I'll keep your response as correct as it answered the initial question in this post.  I just wanted to let you that this idea only works for desktop views.

                    • 7. Re: Value from Previous Date
                      Shinichiro Murakami

                      Sorry I have no idea.

                       

                      To where are you publishing?  Tableau server or online,

                      I don't know the detail background, but did you try to publish Data sources and workbook separately?

                       

                      thanks,

                      Shin

                      • 8. Re: Value from Previous Date
                        Chris C

                        I am publishing to Tableau Server 10.1.3 64 bit.   I tried publishing the data source just now, and I received the same error message.