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:
Quarterly Return.twbx 30.3 KB
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.
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.
1 of 1 people found this helpful
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.
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.
Quarterly Return.twbx 29.9 KB
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.
Book1.twbx 20.9 KB