1 2 Previous Next 16 Replies Latest reply on Jan 26, 2015 10:00 PM by Jagjit Singh

    Calculate difference in value based on two date parameters

    Jagjit Singh

      Hi All,

       

      I have a transaction table and need to calculate the difference in rent amount for a property based on two date parameters. So if the rent of the property was $200/- on 20/03/2014 and $250/- on 18/10/2014 we should +50 in the RentDifference Column. Likewise if the rent was $280 on 13/04/2014 and $150/- on 14/12/2014 we should get -130 in the RentDifference Column.

      Please find attached the sample data.

       

      The view should be as below

      PropCode, Start Date | StartRent,  End Date | EndRent,  Difference in Amount,  Percentage Difference

       

       

       

      Regards,

      Jag

        • 1. Re: Calculate difference in value based on two date parameters
          Chris McClellan

          Is this what you're after ?

           

          http://sqlfiddle.com/#!3/a623f/15

           

          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

          from (

          select prop_code,

          @startdate StartDate,

          (select top 1 RentCharged from test startsub 

          where RentUpdateDate <= @startdate

          and prop_code = test.prop_code

          order by RentUpdateDate desc)

          as StartRent,

          @enddate EndDate,

          (select top 1 RentCharged from test endsub 

          where RentUpdateDate <= @enddate

          and prop_code = test.prop_code

          order by RentUpdateDate desc)

          as EndRent

           

           

          from test

          ) dataset

           

           

           

          That's MS SQL 2008, but can be easily changed to suit another RDBMS.

          • 2. Re: Calculate difference in value based on two date parameters
            Jagjit Singh

            Thanks Chris but was keen on using Tableau to achieve the same.

             

            Regards,

            Jag

             

            Sent from my iPhone

            • 3. Re: Calculate difference in value based on two date parameters
              Prashant Sharma

              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.

              Untitled.png

              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))

              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))

              end

              6) Put your date field into filter section & choose Range of Dates.

              Check the attached file.

              Capture.JPG

              Hope this may help!

               

              Warm Regards,

              Prashant Sharma - India | LinkedIn

              • 4. Re: Calculate difference in value based on two date parameters
                Jagjit Singh

                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.

                 

                Cheers

                • 5. Re: Calculate difference in value based on two date parameters
                  Jagjit Singh

                  Hi Prashant,

                   

                  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.

                   

                  2014 Jan--------June--------Dec

                  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.

                   

                  Cheers

                  • 6. Re: Calculate difference in value based on two date parameters
                    Jagjit Singh

                    Hi Prashant,

                     

                    I have uploaded a sample data set on box: https://app.box.com/s/pu8oa4f3jhrhm0ylshdz2fuo7541vn4z

                     

                    Hope this helps.

                     

                    Thanks

                    • 7. Re: Calculate difference in value based on two date parameters
                      Prashant Sharma

                      Hi,

                      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!

                       

                      Warm Regards,

                      Prashant Sharma - India | LinkedIn

                      • 8. Re: Calculate difference in value based on two date parameters
                        Jagjit Singh

                        Hi Prashant,

                         

                        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.

                        • 9. Re: Calculate difference in value based on two date parameters
                          kettan

                          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>
                            )
                          
                          
                          
                          
                          
                          

                          thread 155030 Calculate difference in value based on two date parameters.png

                          Ps. I used sample data provided in  Re: Calculate difference in value based on two date parameters

                          • 10. Re: Calculate difference in value based on two date parameters
                            Jonathan Drummey

                            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:

                             

                            Screen Shot 2015-01-25 at 12.27.44 PM.PNG

                             

                            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:

                             

                            Screen Shot 2015-01-25 at 12.29.01 PM.PNG

                             

                            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:

                             

                            Screen Shot 2015-01-25 at 12.32.46 PM.PNG

                             

                            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):

                             

                            Screen Shot 2015-01-25 at 12.38.20 PM.PNG

                             

                            Finally, the view can be built entirely without the additional complexities introduced by table calculations:

                             

                            Screen Shot 2015-01-25 at 12.37.31 PM.PNG

                             

                            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.

                             

                            Jonathan

                            • 11. Re: Calculate difference in value based on two date parameters
                              kettan

                              Thank you, Jonathan. This is a precious comment & solution which I hope will be read by many learners.

                              • 12. Re: Calculate difference in value based on two date parameters
                                Jonathan Drummey

                                I think I’ll do a blog post out of it to boost the signal.

                                • 13. Re: Calculate difference in value based on two date parameters
                                  Jagjit Singh

                                  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.

                                   

                                  ErrorMaxRent1.png

                                   

                                  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.

                                   

                                  Total Difference.png

                                   

                                   

                                   

                                   

                                   

                                   

                                  Really appreciate your help on this

                                   

                                  Thanks

                                  • 14. Re: Calculate difference in value based on two date parameters
                                    Jonathan Drummey

                                    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:

                                     

                                    Screen Shot 2015-01-26 at 6.29.38 AM.PNG

                                     

                                    Jonathan

                                    1 2 Previous Next