7 Replies Latest reply on Nov 15, 2018 11:59 AM by Joe Oppelt

    Rent Revenue For The Past Five Years

    Emily Oliver
    Emily Oliver Nov 14, 2018 1:39 PM

      I'm trying to calculate how much rent was collected for the past 5 years from today. The formula I've included represents all rents collected in 2013. When I create another calculated field for the year of 2014 I will just change the YEAR(TODAY()-5) to YEAR(TODAY()-4). I've built similar reports on other platforms using a 'between' function which tableau doesn't have. I think I'm close but I cannot get this formula to work within tableau. The error that pops up says "Expected 'END' to match 'IF' at character 0". Please help!

       

      IF YEAR([Rent Change Effective Date]) < YEAR(TODAY()-5) < YEAR([Lease Expiration Date]) THEN

        IF YEAR(TODAY()-5) = YEAR([Rent Change Effective Date]) THEN (12-MONTH([Rent Change Effective Date]))*[Monthy Rent]

        ELSEIF YEAR(TODAY()-5) < YEAR([Lease Expiration Date]) THEN 12*[Monthy Rent]

        ELSEIF YEAR(TODAY()-5) = YEAR([Lease Expiration Date]) THEN MONTH([Lease Expiration Date])*[Monthy Rent]

      ELSE 0

      END

       

      Thank you.

        • 1. Re: Rent Revenue For The Past Five Years
          Joe Oppelt
          Joe Oppelt Ambassador Nov 14, 2018 1:57 PM (in response to Emily Oliver)

          TODAY()-5 subtracts 5 days from today.

           

          You want to use DATEADD('year',-5,TODAY())

           

          Or DATEADD('day', -1825, TODAY())

           

          (You'll want to make sure the "day" version handles leap years the way you want them to.)

           

          The error you are getting is that you have two "IF" portions of your logic, but only one END.  The question you need to discern is if you want the second END to be BEFORE the ELSE 0 clause, or after it.

           

          I also don't understand what you are looking to do with this part in the first line:

           

          < YEAR(TODAY()-5) < YEAR([Lease Expiration Date])

           

          That's not going to work properly as it stands, I think.

          • 2. Re: Rent Revenue For The Past Five Years
            Emily Oliver
            Emily Oliver Nov 14, 2018 2:37 PM (in response to Joe Oppelt)

            Joe Oppelt Thank you for getting back to me so quickly! The information in the database is listed as follows:

            example.PNG

            The purpose of the first line is to distinguish that I only want to deal with monthly rents for line items where DATEADD('year',-5,today()) is between [Rent Change Effective Date] and [Lease Expiration Date]. (This is after making the change you recommended with the DATEADD formula, good catch).

            For the tenants shown above in the example picture, I would want this calculated field to be called "2013" and it would add up the following monthly rent numbers: $3,333.33*12 + $3,333.33*12 + 2,916.67*5 + 3,333.33*(12-5) + 3,333.33*12.

            I would then have another calculated field that gave me the numbers shown in the 2014 column, however it would contain DATEADD('year',-4,today()), and so on.

             

            Tableau doesn't have a between function so I'm trying to come up with an alternative.

            I think I want the second END to come after the Else 0 in this circumstance, would you agree? Your feedback was really helpful, let me know if I can explain anything else further!

            • 3. Re: Rent Revenue For The Past Five Years
              Joe Oppelt
              Joe Oppelt Ambassador Nov 14, 2018 2:47 PM (in response to Emily Oliver)

              Emily Oliver wrote:

               

               

              ... I only want to deal with monthly rents for line items where DATEADD('year',-5,today()) is between [Rent Change Effective Date] and [Lease Expiration Date]. (This is after making the change you recommended with the DATEADD formula, good catch).

              ...

               

              Tableau doesn't have a between function so I'm trying to come up with an alternative.

               

               

              I think I want the second END to come after the Else 0 in this circumstance, would you agree? Your feedback was really helpful, let me know if I can explain anything else further!

              To do a "between", break it into 2 "AND" halves.

               

              IF DATEADD('year',-5,today()) >= [Rent Change Effective Date] AND DATEADD('year',-5,today()) <=  [Lease Expiration Date] ...

               

              The >= syntax means "less than or equal to".  (You may or may not want that.  It just matters if you want those two comparison dates to be included or excluded from the range.)

               

              The extra END depends on what you expect the ELSE 0 to be part of.  Usually it's the last part of ENDIF logic, so yes, you probably want that END to go after that.

               

              If you have those two ENDs together at the end, then there is no ELSE half to the first line of the equation.  That's OK.  Tableau inserts NULL when no ELSE is specified, but an ELSE condition occurs.  (So in this case, all non-5-year data will end up with NULLs for this calc.  Which is perfectly OK.  I use that all the time.)

              • 4. Re: Rent Revenue For The Past Five Years
                Michael Gillespie
                Michael Gillespie Nov 14, 2018 2:56 PM (in response to Emily Oliver)

                Emily, we do "between" by specifying the 2 conditions that apply joined by an AND.

                 

                So:

                IF TODAY() > [Rent Change Date] AND TODAY() < [Lease End Date]

                THEN <SOME CALCULATION>

                END

                 

                Change those values to be the REAL field names & date ranges you want, of course.

                 

                Make sense?

                • 5. Re: Rent Revenue For The Past Five Years
                  Michael Gillespie
                  Michael Gillespie Nov 14, 2018 2:56 PM (in response to Michael Gillespie)

                  And for the 2nd time today, do what Joe said!

                  • 6. Re: Rent Revenue For The Past Five Years
                    Emily Oliver
                    Emily Oliver Nov 15, 2018 10:47 AM (in response to Joe Oppelt)

                    Joe Oppelt & Michael Gillespie

                     

                    I think I'm really close with this formula thanks to the help from you guys! Here's where I'm at...

                     

                    IF DATEADD('year',-5,today()) >= YEAR([Rent Change Effective Date]) AND DATEADD('year',-5,today()) <= YEAR([Lease Expiration Date])

                    THEN

                         IF DATEADD('year',-5,today()) = YEAR([Rent Change Effective Date]) THEN (12 - MONTH([Rent Change Effective Date])) * [Monthly Rent]

                         ELSEIF DATEADD('year',-5,today()) < YEAR([Lease Expiration Date]) THEN 12 * [Monthly Rent]

                         ELSEIF DATEADD('year',-5,today()) = YEAR([Lease Expiration Date]) THEN MONTH([Lease Expiration Date]) * [Monthly Rent]

                    ELSE 0

                    END
                    END

                     

                    Now I'm getting the following error: "can't compare datetime and integer values." I guess I thought that I was working with only datetime values here, am I mistaken? What can I do to convert all values appropriately?

                     

                    Thank you!

                    • 7. Re: Rent Revenue For The Past Five Years
                      Joe Oppelt
                      Joe Oppelt Ambassador Nov 15, 2018 11:59 AM (in response to Emily Oliver)

                      Take YEAR off those date fields.  You have a date field from the DATEADD.  Just compare the DATEADD date to the actual begin and end dates.