3 Replies Latest reply on Jul 21, 2016 9:02 AM by David Li

    Count house inventory

    Rachel Li

      Hi All,

       

      I need to count house inventory.

      All listing houses have their own housing IDs. There are two status:active and closed.  There are two dates: listing date and close date.

      If it's active, it should be count as inventory for the month it was listed.

      If it's closed, it should be count as inventory between the months it was listed and closed. (This is the tricky part.)

      The result can be grouped by the month of listing date field.

      I have attached workbook and hope someone can find a solution.

       

       

       

       

      Thanks very much.

        • 1. Re: Count house inventory
          David Li

          Hi Rachel! This is an interesting case and I have a solution as long as you're willing to reshape your source data.

           

          Basically, instead of a source with each house to a line, we use a source that has a line for each event (listing or closing). That gives us something like this:

          housedemo.jpg

          This is the kind of data source that Tableau likes, and it's trivial to build based on what you already have. We build our sheet with MONTH(DATE) along the columns so we have individual months in each column. In order to count inventory, we're going to use a running sum. We add 1 for every "Listed" event and we subtract 1 for every "Closed" event. This is pretty simple, and you can just create an IIF() statement:

           

          IIF([Event] = "Listed", 1, -1)

           

          Then, just do a running sum of that across months. Pretty simple.

           

          When I first read your post, I might have misunderstood you and thought that open houses (that aren't Closed) count as inventory only for the month when they were listed. That's actually a more interesting problem to solve. We can tackle it like so:

           

          We need to subtract the inventory for Listed houses the month after they were listed (see my assumption above). To do that, we need our viz to see the future and see if the house has a Closed status later. We can do this with an LOD calculation:

           

          { FIXED [ID] : SUM(IIF([Event]="Closed", 1, 0)) } = 1

           

          This will be TRUE if the house has Closed, even if we're not looking at the month when it closed. Now, we can use this as our cool running total, making sure to subtract the open listings from the previous month:

           

          RUNNING_SUM(SUM([Tally])) - ZN(LOOKUP(SUM(IIF([Event] = "Listed" AND NOT [IsClosed], 1, 0)), -1))

           

          Now we just put that into the Text Mark, and voila!

          1 of 1 people found this helpful
          • 2. Re: Count house inventory
            Rachel Li

            Thanks David. This is an amazing solution.

            I think we can pivot the data first inside tableau and just do exactly what you wrote down here.

            I actually needed the solution two, so you didn't misunderstood my question.

             

            How difficult do you think this question is? I just want to have an idea of where I am at. : )

            • 3. Re: Count house inventory
              David Li

              You're welcome, Rachel! I'm glad that was helpful and that I didn't misunderstand.

               

              I think that this is a relatively difficult problem that would be hard to solve without a good understanding of LOD calcs and table calcs, which are both advanced topics. So you're definitely getting your Tableau on!