1 Reply Latest reply on May 7, 2012 3:01 AM by Richard Leeke

    Days since last date there is an entry for

    Andrew Fisher

      Hi all,

       

      I have a data set that's quite holey in several respects. First of all I don't get the data every single day so it's probably 4 in 7 days on average I have data for. I definitely DON'T get it over the weekend but I may not get one or two days through the week on an average week as well (it's all dependent on warehouse cycle counting).

       

      The data I get is a report for a given day showing a set of products and their current level of stock.

       

      The second hole comes from not necessarily getting a count of stock for every product on every day a report is created as stock doesn't change if a product hasn't sold.

       

      As such if I want to show what is in the warehouse TODAY I may have some data from today's report, but I may also have to go back a couple of days if there isn't a report for today and additionally the report from that day may not include certain skus so I may have to look further back.

       

      SO

       

      Given a set of data that looks like that attached where I have each row providing a date, a product and a stock level how do I output something that shows me the LAST available stock level and the date of when that was.

       

      IE given the data set I should get:

       

      Product     SOH     Date

      A              5          3/5/12

      B              5          1/5/12

      C              6          6/5/12

      D              5          6/5/12

       

       

      I was trying to use a filter approach but couldn't work out how to keep going back through days until I found the one I'm looking for based on a row.

       

      If I was doing this in code I'd just go:

       

      For each product in product_list:

           Get Max(Date) from stock list

           Get SOH[MAX(Date)] from stock list

       

      And that would do it.

       

      Any help would be greatly appreciated.

       

      Cheers

      Andrew

        • 1. Re: Days since last date there is an entry for
          Richard Leeke

          There are a few approaches you can use here using table calculations, data blending, custom SQL or RAWSQL.

           

          I started putting an example together using the Tableau coffee sales data, but got distracted by this issue with the approach I was trying to use with table calculations, which means that it no longer works in version 7.0.

           

          So for now I've just attached a workbook with your sample data showing how to do it with a simple custom SQL connection to get the latest dates and then data blending to find the associated stock on hand. I'm fairly sure it would still be possible with a different approach with table calculations, but I ran out of energy. There are lots of other ways to do it, I'm sure.