3 Replies Latest reply on Jun 13, 2016 3:21 PM by Ivan Young

    Replacing null values with last known values in row

    Nik Agarwal

      I have data on several machines that report their odometer (in minutes) by week. I've created a worksheet that shows the different machines in rows, the week number in columns. Normally, I would get this data from our SQL server, but for this forum post, it's just in Excel format.


      What I'm being asked to complete:

      • Create a grid that shows the odometer for every week (replacing null values with the last known value)
        • this is broken out by machine
      • Create a difference in total odometer for every week (this would be aggregated by week)


      Here's what I have so far in the worksheet (figure 1):




      I realize this is kind of an unusual task. Here's what I think the final deliverable will work like (figure 2):




      What I'm being asked to deliver is this (figure 3):



      The fields in yellow highlight are replacing the null value with the last known value.


      I realize that figure 3 may not necessarily be feasible and I would be ok with figure 2. What would be great is creating figure 3 without the totals or difference. I've attached my workbook extract. Please let me know if additional information is needed. I am currently using Tableau 9.2.3. I do apologize in advance for any shortcomings/oversights in this post. This is my very first post here.



        • 1. Re: Replacing null values with last known values in row
          Ivan Young

          Hi Nik,

          Very nice and articulate post, you've explained your issue clearly and set very realistic expectations.


          I can get you pretty darn close to figure 3 and it may be possible to achieve perfectly, coloring and conditional formatting is not one of my strengths.  I couldn't get the borders and colors to work together, if I just colored the text I could get the borders.  You may want to start a separate post on conditional formatting if you feel it's important.


          Below is a brief description of my method:


          1.  Created [Machine Odometer Calc]:  ZN(IFNULL(SUM([Machine Odometer]),PREVIOUS_VALUE(SUM([Machine Odometer]))))  - This will populate your table with the values you desire.


          2.  Replace your current measure with [Machine Odometer Calc]


          3.  Created an LOD I used for coloring(Note that I'm no expert on colors) [Coloring Measure]:  {Fixed [Week Number], [Machine] : SUM([Machine Odometer])}.  Take a look at how I set up coloring, I can't really explain it well.


          4. Create a separate sheet for your totals and difference.  This is just a SUM(Machine Odometer) by week and a table calc to generate the difference.


          5.  Stack the two sheets in a dashboard.


          Please let me know if you have any questions.




          1 of 1 people found this helpful
          • 2. Re: Replacing null values with last known values in row
            Nik Agarwal

            Hello Ivan - this was exactly what I was looking for. The yellow highlights that I had made were only there to point out the changes between figures 2 and 3. My mistake on not clarifying that earlier and having you go the extra mile on creating conditional colors. I was able to follow along with your methodology quite well - so thank you for explaining the process.

            • 3. Re: Replacing null values with last known values in row
              Ivan Young

              Lol,  That's pretty funny about the coloring, although it was fun and good practice since I don't work with colors much  I'm glad you were able to implement the method .