-
1. Re: Rent Revenue For The Past Five Years
Joe OppeltNov 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 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:
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 OppeltNov 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 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 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 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
ENDNow 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 OppeltNov 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.