4 Replies Latest reply on Jan 25, 2012 8:36 AM by Jonathan Drummey

    Difference Table Calculation

    Christopher Henderson



      If I have the selling price of products by week how do I ensure that the difference calculation of the last recorded value, rather that counting null values as 0 - which is what is happening currently.


      My data is structured like



      Week 1    £1.00

      Week 2    £1.00

      Week 3

      Week 4    £1.20

      Week 5    £1.25


      Basically in the difference calculation it is counting week 3 as a variance of £1.00 whereas I want it to refer to the previous recorded value so in this instance the difference will be £0.


      Hope this makes sense.


      Help much appreciated.



        • 1. Re: Difference Table Calculation
          Christopher Henderson

          My data looks like attached - I want to add in a calculation that looks at the difference between the latest week figure, and the week before the latest week. Any thoughts on how this can be done?

          • 2. Re: Difference Table Calculation
            Jonathan Drummey

            Based on your description, it's pretty straightforward to use the LOOKUP() function to get the previous week's information, and then use that to generate either an absolute or % difference. I've attached a workbook that shows both.


            Filling in the missing weeks with the most recent week's number is more problematic. It's possible in Tableau toset up a series of IF statements to look at the current value, if that's Null then look for the prior value, if that's Null then look at the next prior value, etc. I did this in the workbook for one level of missing values to show the concept. You'd have to define enough IF statements to deal with however many missing weeks you might have in your data, which would get ugly quickly (Tableau doesn't support any recursion functions). The better bet would be to preprocess your data prior to Tableau by constructing a set of queries that fill in the missing values, if you haven't done this before then you can do a web search and get a variety of algorithms.


            Also, as a more general request, please provide sample data in addition to or in place of a screenshot, so that way folks like me can more quickly get to work answering your question.





            • 3. Re: Difference Table Calculation
              Christopher Henderson

              Thanks Jonathan - Here is the example workbook that I have made.

              • 4. Re: Difference Table Calculation
                Jonathan Drummey

                Hi Christopher,


                I looked over the workbook and applied some calculations as described, here it is. Given the number of weeks without sales, I think you're probably better off doing the calculation to get the prior value outside of Tableau, otherwise you'll need 51 levels of an IF statement.


                Also, some of the sales have a 0 amount with 0 units, I imagine that's just because you were making up dummy data? That makes display interesting depending on whether you want Null values to be 0 or not be displayed, you can see this in the results for on Sold-to code 0160483, Product A.