Is this what you're after ?
In case that link disappears, I turned your data into a table and then wrote this query:
-- PropCode, Start Date | StartRent, End Date | EndRent, Difference in Amount, Percentage Difference
declare @startdate date
declare @enddate date
set @startdate = '11/24/2011'
set @enddate = '11/28/2013'
select prop_code, StartDate, StartRent, EndDate, EndRent, EndRent-StartRent DollarDiff, (EndRent-StartRent)/StartRent PctDiff
(select top 1 RentCharged from test startsub
where RentUpdateDate <= @startdate
and prop_code = test.prop_code
order by RentUpdateDate desc)
(select top 1 RentCharged from test endsub
where RentUpdateDate <= @enddate
and prop_code = test.prop_code
order by RentUpdateDate desc)
That's MS SQL 2008, but can be easily changed to suit another RDBMS.
Thanks Chris but was keen on using Tableau to achieve the same.
Sent from my iPhone
Here are the steps -
1) Put Prop Code into Rows, Rent Update Date into Mark Section
2) Create two calculated field for Minimum Date & Maximum Date
window_min(min([Rent Update Date]))
window_max(max([Rent Update Date]))
3) Put these two fields into rows & compute it using Rent Update Date.
4) Now create following field -
if first()== 0 then window_sum((if last()==0 then sum([Rent Charged]) end))
-window_sum((if first()==0 then sum([Rent Charged]) end))
This field is "Difference in Amount" & compute this field also according to "Rent Update Date".
Also, put this field into filter & choose 'Non-Null' field.
5) For % difference -
if first()== 0 then
(window_sum((if last()==0 then sum([Rent Charged]) end))-(window_sum(if first()==0 then sum([Rent Charged]) end)))
/(window_sum(if first()==0 then sum([Rent Charged]) end))
6) Put your date field into filter section & choose Range of Dates.
Check the attached file.
Hope this may help!
Thanks Prashant,this helped. I had to changed the number format for the Percentage difference to Percentage to get the values. I see in your workbook, the values appear correctly even if the number format is set to automatic. Not sure why I had to specifically set it to percentage.
Also I like to show the start rent amount and End Rent Amount for better clarity and if you can help with that please.
After working with the workbook I realised the difference is amount is showing between the last rent updates only.
The excel sheet attached above is the rent master table which holds information when the rent was updated for a property. So after the rent update, the new rent will apply to the property till then next rent update, which is done every 6 months.
For example lets say the initial rent for a property starts at $100 in Jan 2014 and after 6 months the rent upto $150, which means the rent from Jan to May 2014 was S100. So when we compare the difference in rent between any dates in March and June it should $100-$150 and the difference as -50.
100 150 180
2015 Jan------June ------Dec
180 210 220
1) Rent compared in March 2014 and 2015 = 100-180 = 80
2) Rent compared between August 2014 and August 2015 = 150-210 = 60
Hope I was able to explain this clearly.
Now I got your query. Following are the steps -
1) Now, you have to create parameters as quickfilter filters the values from worksheet & we need previous value here.
Create two parameters, one is Start Date & one is End Date.
2) Create calculated fields for start date & end date.
For Start Date -
max(if [Rent Update Date]=[Parameters].[Start Date] then [Rent Update Date] elseif
[Rent Update Date]<[Parameters].[Start Date] then [Rent Update Date] end)
For End Date -
max(if [Rent Update Date]<[Parameters].[End Date] then [Rent Update Date] end)
3) Now create Starting Rent & Ending Rent.
Starting Rent -
max(if [Rent Update Date]=[Parameters].[Start Date] then [Rent Charged] elseif
[Rent Update Date]<[Parameters].[Start Date] then [Rent Charged] end)
Ending Rent -
max(if [Rent Update Date]<[Parameters].[End Date] then [Rent Charged] end)
4) Now you have to create Different in Rent -
[End Rent]-[Start Rent]
5) Now % Difference -
([End Rent]-[Start Rent])/[Start Rent]
Also, check the "With Parameter" sheet of the attached workbook. Hope this may help!
While you got what I'm trying to achieve the results for certain date parameters do not match. If I select the end date as 31/12/2014, the End Rent is the max rent in year 2014, which is not correct. The end rent should be the last rent amount in dec. Similar if the start date and end date were 31/12/2014, the start rent and end rent should have the same value. I have uploaded the revised sample data and workbook.
I don't have a solution for this without using custom SQL and therefore follow this thread with curiosity to see if anyone will show how to do this 100% inside Tableau, that is 1) without custom SQL and 2) without pre-reshaping the data.
That said, it is possible to reshape the data with a few subqueries in a custom SQL with parameters:
SELECT * , ( SELECT MAX(t2.[Rent Change Date]) FROM [Sheet1$] t2 WHERE t2.[PROP_CODE] = t1.[PROP_CODE] AND t2.[Rent Change Date] <= <Parameters.End Date> ) AS [End Rent Change Date] , ( SELECT t2.[Rent Charged] FROM [Sheet1$] t2 WHERE t2.[PROP_CODE] = t1.[PROP_CODE] AND t2.[Rent Change Date] = ( SELECT MAX(t3.[Rent Change Date]) FROM [Sheet1$] t3 WHERE t3.[PROP_CODE] = t1.[PROP_CODE] AND t3.[Rent Change Date] <= <Parameters.End Date> ) ) AS [End Rent Charged] FROM [Sheet1$] t1 WHERE t1.[Rent Change Date] = ( SELECT MAX(t2.[Rent Change Date]) FROM [Sheet1$] t2 WHERE t2.[PROP_CODE] = t1.[PROP_CODE] AND t2.[Rent Change Date] <= <Parameters.Start Date> )
Ps. I used sample data provided in Re: Calculate difference in value based on two date parameters
Here's a take at it that uses roughly the same technique as Prashant's.
For the start & end date I used parameters, and the Prop Code & Rent Update Date are in the view as dimensions. All the calculations have a Compute Using on the Rent Update Date. The chosen start date rent value uses the following calc:
PREVIOUS_VALUE(WINDOW_MAX(MAX(IF [Rent Update Date] <= [Min Rent Update Date] THEN [Rent Update Date] END)))
The Min Rent Update Date is the parameter. The innermost part is evaluated for each record and returns a number of values. This is wrapped in a MAX so we can use it in a table calc (necessary for the later bit), ATTR() or MIN() would work just fine because the Rent Update Date is a dimension in the view. Then the WINDOW_MAX() gets the maximum value cross all Rent Update Dates (for each Prop Code, based on the Compute Using of the Rent Update Date), then PREVIOUS_VALUE() is an optimization so this is compute only once for each address.
There's a similar calc for the End Date.
Here's the Start Rent calc: PREVIOUS_VALUE(WINDOW_MAX(IF [Start Date] == ATTR([Rent Update Date]) THEN SUM([Rent Charged]) END)) Because we have a Start Date determined, we can use a similar IF statement to return the Rent Charged for the Start Date, then the WINDOW_MAX() returns that to every address.
With a similar calc for the End Rent, then the difference & % difference can be computed.
Here's the workout worksheet:
For the final view, I duplicated the workout worksheet, got rid of the ATTR(Rent Update Date pill), added a FIRST()==0 calc with a Compute Using on the Rent Update Date onto the Filters Shelf, and turned off the tooltip for Rent Update Date:
This is a great example of how we can do aggregates of aggregates using table calculations in Tableau, and also an example of how complicated this can be.
Tableau v9 (now in Beta) will make this much easier with a new kind of calculation called a "Level of Detail" calculation. These work similar to computed Sets in that they let us perform an aggregation calculation per distinct value of a dimension only where computed Sets simply return a boolean In/Out, LOD calcs let us return a variety of values. Here's the LOD calc to get the End Date:
The way to read this is: for each Prop Code, return the latest Rent Update Date that is earlier than or equal to the entered parameter.
This is then available as a dimension, which means we can do a record-level evaluation to return the start rent (whereas in the prior solution that was actually a table calculation start date being compared to an aggregate rent update date):
Finally, the view can be built entirely without the additional complexities introduced by table calculations:
And yet another benefit is that we can then use table calcs if we want without getting into things like nested table calcs. For example, computing z-scores on the % difference would be relatively simple in this case -- [% Difference] - WINDOW_STDEV([% Difference]) -- whereas in the original table calc solution we'd have to rewrite the existing table calcs to not double-count each Prop Code (based on the presence of the Rent Update Date dimension in the view), use a nested calc to compute the standard deviation, then yet another nested calc to compute the z-score. The way I see it, LOD calcs let us "push down" aggregations over our data (from table calcs into regular aggregates or record-level calcs) so we can more easily end up with the results that we want in Tableau.
In fact, LOD calcs are so powerful that in a number of cases we'll be able to completely avoid table calcs. In the above z-score example, that could be entirely computed in LOD calcs if we wanted. The big unknown of LOD calcs is performance, since they are issuing subqueries, and the challenge with LOD calcs is that they do require a solid grasp of aggregation, since the UX for them is entirely about writing calculated fields.
rent v8.3.twbx 80.4 KB
Thank you, Jonathan. This is a precious comment & solution which I hope will be read by many learners.
I think I’ll do a blog post out of it to boost the signal.
Thanks Jonathan. For propcode 1008 if you put in 31/12/2014 in the max rent update date, the amount 206.38 appears but the actual value should be 209.04 as you will see in the sample data excel file.
Also can we add columns startdate and enddate similar to kettan workbook or in your workbook if the start date and end date can be equal to the dates in the parameter controls.The idea is to show the individual property rent change in a given period and the total difference as shown below.
Really appreciate your help on this
I used the RentReview.xlsx file that you originally posted. In that file, the most recent rent update date for 1008 is 6/16/14, and the rent for that date is 206.38.
As for using the parameters, all you need to do is create two calculated fields, one for each parameter, then put them in the headers instead of the table calculations: