7 Replies Latest reply on Apr 7, 2016 5:23 PM by Jerry Li

    Calculate % difference on Last Day of Quarter

    Jerry Li

      I have a column [Daily Return], it is the daily growth %.

       

      I create a field called "Daily Score", it was calculated based on the [Daily Return], set the first day score to 100.

       

      Now I want to show the quarterly return:

      (Daily Score on Last Day of Quarter / Daily Score on Last Day of Previous Quarter) - 1

       

      The calculation I used for [Quarterly Return] is wrong, my current code will always use 100 (the first row in that partition) as the denominator, it should be the score on last day of previous quarter.

       

      Please see attached workbook.

       

      My calculation for Q1 2008 (3/31/2008) is correct:   97.545 / 100 - 1 = 0.025

      But starting Q2 2008 (6/30/2008), it is wrong, it should be:  92.379 / 97.542 - 1 = -0.05

       

      1.JPG2.JPG

       

       

      Pooja Gandhi

      Joshua Cloud

      Joe Oppelt

        • 1. Re: Calculate % difference on Last Day of Quarter
          Joshua Cloud

          Jerry,

           

          I have a rather nasty formula for you, it took me a while puzzle out.

           

           

          It looks up the daily score by the number of days between the last day of current quarter and previous quarter, in this example its either 91 or 92 days.

          You will also need to show missing values in the rows shelf for Day(Date) for the calculation to work.

           

          Let me know if this doesn't work for you.

          I used some of the tips found here:

          Current vs Previous Period to Date Comparison

          1 of 1 people found this helpful
          • 2. Re: Calculate % difference on Last Day of Quarter
            Jerry Li

            Joshua, thanks for your solution.

             

            Yes, it worked great. I need some times to understand the your code and the reference post you mentioned. The code is quite complicated, but solve the problem though.

             

            I hide the dates that are not the last day of each quarter, and this is what finally I need.

            1.JPG

             

            Follow up question:

            In your Lookup function in the Quarterly Return cal field, will it correctly detect the number of days between last day of current quarter and last day of previous quarter?  The sample workbook I posted initially only include data from 2008.

            • 3. Re: Calculate % difference on Last Day of Quarter
              Joshua Cloud

              It should correctly detect the number of days. I tried to provide some notes to the formula.

              The only thing that bothers me is that I don't understand why

                   INT(DATEADD('quarter',1,DATETRUNC('quarter',[Date]))- 1) - INT(DATETRUNC('quarter',[Date])- 1)

              actually provides 2 values, one that is correct and the other that applies to a null [Last Day of Quarter], (i.e. 91 correct, 92 null), when I used Max() it appears to use the correct value even if it is the lower of the two values.

              1 of 1 people found this helpful
              • 4. Re: Calculate % difference on Last Day of Quarter
                Jerry Li

                Hello Joshua,

                 

                Your method is working but there is a problem with showing missing value, if I want to display Q1 2008 instead of exact date 3/31/2008, the date will aggregate the entire data for Q1 in 2008 which includes the value that is missing even I hide them.

                • 5. Re: Calculate % difference on Last Day of Quarter
                  Joshua Cloud

                  Jerry,

                   

                  Try adding the quarter of date to the rows like below:

                  Then right click on the date in the Day of Date column and uncheck the Show Header.

                  To get this view:

                  Let me know.

                   

                  Cheers!

                  1 of 1 people found this helpful
                  • 6. Re: Calculate % difference on Last Day of Quarter
                    Jerry Li

                    Hi Joshua,

                     

                    Thanks for your help and I learned new stuff. I have another similar issue:

                     

                    This is "year return", think it as a investment account, it opened on 2/7/2007, so the return for 2007 should be (last day of 2007)/(first day of 2007) - 1

                    But the following years the calculation will be just the % difference on the last day of each year.

                    I add a new year of date:

                    And uncheck the header of the original Date, I have some empty space over 2007, are they any way to have 2007 row to be "normal"? I can't exclude that empty space as it will exclude 2/7/2007 so the calculation will be wrong.

                     

                    Attached a 9.3 workbook.

                     

                    Thanks so much!

                     

                    Basically I want a table with two column, Year and Return.

                    The trick is the first year 2007 will use two data points within that year, but following years will use one data point (last day) from current year and one data point (last day) from previous year.

                    • 7. Re: Calculate % difference on Last Day of Quarter
                      Jerry Li

                      I figured it out! haha.

                       

                      hold ctrl and move the quick table cal SUM(Price) to filter section, go to Special - > choose Non-null values.

                       

                      Thanks for your help!!