3 Replies Latest reply on Aug 10, 2016 1:07 PM by swaroop.gantela

    how to calculate cost streams over time

    Elizabeth Betts

      Hi,

       

      I’m trying to show our budget (both past and future) and I’m struggling.  All of the budget examples I found have a single date for their payments while I have a start and expiration date for scheduled on-going payments.

       

      I’m attaching a sample of my data.  I work in real estate and I have rent streams for each of our properties.  A rent stream consists of a few key elements including (per the columns in the
      attached .xls file)

      1. The site unique ID
      2. The amount of the payment
      3. The reason for the payment
      4. The frequency of the payment (0 is a onetime payment on the commencement date, 1 is a monthly payment, 12 is an annualpayment  - the frequency represents the number of months in the period)
      5. When we started making the payments (commencement date)
      6. When the payment are scheduled to end (expiration date)

       

      Per my attached tableau workbook I have been able to graph my data at the year level, but it’s not quite right on 2 accounts:

      1. The results per year are based on the monthly cost of the rent steams in that year… however, I don’t know how to take month timing into account.  For example, I have a rent streams on CO001499 starting on 11/1/2007 totaling $2,660 per month.  I would like to show that in 2007 we paid $5,320 (November and December at $2,660 each).  I don’t know how to make this adjustment without impacting the whole rent stream (via tableau’s “Running_Sum” for future years (eg: all of those rent streams are active in 2008 so we paid $31,920 that year - $2,600 * 12 months).  This same issue is in reverse for when leases expire.
      2. I’m not quite sure how to deal with frequency… I think it needs to fold into the same equation as will be used for monthly payments

       

      I had considered using these formulas to come up with a monthly sum.  But again, these recalculate the whole rent stream and thus impact each of the years (not just the start / stop year).

       

      For when leases start: ([Amount]/[Frequency])*(13-month([Commencement]))

      For when leases end: ([Amount]/[Frequency])*month([Expiration])

       

      FYI: I modeling my work on this article…

      http://kb.tableau.com/articles/knowledgebase/show-records-within-period-of-time

       

      Any help would be greatly appreciated.

      Thank you,

      Beth

        • 1. Re: how to calculate cost streams over time
          swaroop.gantela

          Elizabeth,

           

          I just looked at a few aspects of your post, but maybe the attached can be a first step.

           

          This is based off of kettan's excellent post:

          CROSS JOIN with Tableau's join dialog

           

          This I think will help with getting intermediate months (i.e. November and December of 2007).

           

          A new sheet (Lookup) was added to your spreadsheet (thank you for it!) which has one column of a Key of all 1s

          and another column of all the possible dates (in the form of a start of a month 1/1/2007, 2/1/2007, etc.) upto 12/1/2016.

           

          This Key column of 1s was also added to your data sheet.

           

          Because of the key of 1, the joining of the two sheets creates every combination of your data rows and every lookup date.

          So what is needed next is a filter to just get the lookup dates that are between commencement and expiration:

          ([Lookup Date]>=[Commencement]

          AND [Lookup Date]<=[Expiration])

          OR

          ([Lookup Date]>=[Commencement]

          AND ISNULL([Expiration]))

           

          The second part of the OR is for those that do not have an expiration date.

           

          Now you can plot your amounts versus the LookupDate and will get both November and December.

           

           

          With respect to the frequency issue, you could try a calculated field that just returns the [Amount]

          for a 0 frequency on the month that it commenced, or for a 12 frequency, every year on that month until expiration:

          IF [Frequency]=0 THEN

              IF DATETRUNC('month',[Lookup Date])=DATETRUNC('month',[Commencement])

              THEN [Amount]

              END

          ELSEIF [Frequency]=12 THEN

              IF MONTH([Lookup Date])=MONTH([Commencement])

                  AND YEAR([Lookup Date])<=YEAR([Commencement])

              THEN [Amount]

              END

          ELSE [Amount]

          END

           

          The DATETRUNC part says just the same month and year as commencement

          and the Month - Year one says the same month but every year up to expiration.

          212158rent.png

          1 of 1 people found this helpful
          • 2. Re: how to calculate cost streams over time
            Elizabeth Betts

            Gantela, thank you for your response, your message was exactly correct.  I really appreciate you getting back to me so quickly and resolving this.

             

            Thank you so much,

            Beth

            • 3. Re: how to calculate cost streams over time
              swaroop.gantela

              Beth,

               

              Glad it worked for you.

              All the best.