Apex Code Development You need to sign in to do that
Don't have an account?
MATTYBME Custom Report Formula for Bookable Business in a given month?
I have a Line Item Custom Object on Contracts. It allows our Sales Team to book line items against a contract. There maybe multiple line items all with differing Start and End Dates, Length of Days, Total Video Streams(over that period) and Streams per day.
We want to do something with a Report that will show Booked Streams for a given month.
Example:
Contract A
Example 1 - with field names
Line Item 1 - Start Date(Start_Date__c) = 1/28/2009 - End Date(End_Date__c) = 6/12/2009 - Length of Days(Length_of_Days__c this is a formula field - ( End_Date__c - Start_Date__c )+1) = 136 - Total Video Streams (Total_Line_Item_Streams__c) = 5,000,000 - Streams per Day(Streams_per_Day__c this is also a formula field - Total_Line_Item_Streams__c / Length_of_Days__c) = 36,765
Line Item 2 - Start Date = 3/1/2009 - End Date = 7/31/2009 - Length of Days = 153 - Total Video Streams = 5,000,000 - Streams per Day = 32,680
How can my Report show how many Streams are booked for the Month of April? Is it at all Possible?
Alright, I have a formula that will compile for you. This should get you all set but I did not actually perform testing other than to make sure it compiled and calculated values. In theory, this should do the job but you need to check it out and see if it performs as expected. I will post the entire formula here and then explain what it is doing and then explain what you need to know about it moving forward.
IF(((MONTH(End_Date__c)) = (MONTH(TODAY()))&&((MONTH(Start_Date__c)) <> (MONTH(TODAY())))), (Round(Day(End_Date__c),0))*(Total_Line_Streams__c /((End_Date__c - Start_Date__c)+1)),
IF((MONTH(Start_Date__c)) = (MONTH(TODAY()))&&((MONTH(End_Date__c)) <> (MONTH(TODAY()))),
((Round(((IF(((MONTH(TODAY()))=1),(DATE(2009,1,31)),
IF(((MONTH(TODAY()))=2),(DATE(2009,2,28)),
IF(((MONTH(TODAY()))=3),(DATE(2009,3,31)),
IF(((MONTH(TODAY()))=4),(DATE(2009,4,30)),
IF(((MONTH(TODAY()))=5),(DATE(2009,5,31)),
IF(((MONTH(TODAY()))=6),(DATE(2009,6,30)),
IF(((MONTH(TODAY()))=7),(DATE(2009,7,31)),
IF(((MONTH(TODAY()))=8),(DATE(2009,8,31)),
IF(((MONTH(TODAY()))=9),(DATE(2009,9,30)),
IF(((MONTH(TODAY()))=10),(DATE(2009,10,31)),
IF(((MONTH(TODAY()))=11),(DATE(2009,11,30)),
IF(((MONTH(TODAY()))=12),(DATE(2009,12,31)),Today()))))))))))))) -(Start_Date__c +1)),0))*
( Total_Line_Streams__c / ((End_Date__c - Start_Date__c )+1))),
IF ((((MONTH(Start_Date__c)) = (MONTH(TODAY())) &&((MONTH(End_Date__c)) = (MONTH(TODAY()))))),
Total_Line_Item_Streams__c , 0)))
Ok, this formula breaks down into three portions. The first section (before the first space) evaluates if the End Date is in the current month but the Start Date is not. If that is the case, it simply multiplies the the Daily Stream Volume (I calculated it here rather than using your formulas, that way it's not dependent on the formulas in case you want to manipulate them later on and not affect this calculation) by the days in the current month (simply derived from the day portion of the End Date.
The second section flips the first and evaluates if the Start Date is in the Current month but the End Date is not. This is where it gets tricky.
This section subtracts the start date from a generic date value that equals the end date of the current month. So, if the current month is April, this determines that and builds 4/30/09 as a value and subtracts the Start Date from it. This gives the formula a number of days that can then be multiplied against the Daily Stream Volume.
I did build a formula that would have derived this value against the current year but it was much too big to compile and hit the Formula Governor. Therefore, YOU NEED TO UPDATE THESE VALUES COME JANUARY OF 2010. You simply replace "09" with "10" and, if it's a leap year, replace Feb's last day value with 29.
I'm pretty sure there is a more elegant method of calculating this value but I just took this approach because I knew I could get you up and running for the year. If anybody else knows a sleek formula for constructing a final date for the current month that does not require manual updating over time, please share it.
Finally, the final section evaluates if BOTH the Start Date and End Date are in the current month. If this true, it just displays the Total Line Item Streams.
Hopefully, this does the trick and your user base rejoices and takes you out for lunch.
If you want a pipelining sort of formula that looks the past the the current month, you probably have to build a series of formulas based off of this one by adding month values to the current month or saying something like the following:
IF((MONTH(End_Date__c)) = 4), Etc.
Keep in mind, you can always update formula fields temporarily. That way, you can take this formula and change it briefly to recalculate for a future month if you want to, say, look at July's projected delivery volume.
All of that being said, you should be able to roll this field up to the parent object. Once you do that, just output this to a report for the current month (it won't work for anything other than the current month) and you should have what you are looking for.
Let me know if things go awry.
All Answers
This makes a lot more sense to me now (Matty had a post on Idea Exchange about this http://ideas.salesforce.com/article/show/10096352/Reports_to_show_MonthbyMonth_activity_even_if_Report_time_frame_is_a_previous_Month).
Just so I am clear on the requirements, you want to total all the Streams that will occur in a month. Is it safe to assume to base this on the average streams per day or do you have actual values for a single booked stream that are different for different days?
If I understand correctly, you want a total of (Streams Per Day times Days Active in a Month) grouped by month.
So if something lasts half a month (15 Days) and has a SPD value of 100 and something else lasts the whole month and also has SPD of 100, you would expect your report to show Booked Streams for that month= 4500, correct?
Crmninja - the Streams per Day is a division of Length of Days (Calculation of days between the Start and End dates) by the Total Streams.
So yes, this is correct :
Ok, I threw something together very quickly and did not quality test it but it will serve as a decent starting point to help you.
All it would do (when it's finally working :smileywink:) is give you the total Streams for the Current Month. If you wanted one for next month (or any other month) you would have to add that calculation into this formula or another(which would require a separate report. There's a method for calculating against any month, regardless of the current month (But I didn't have time to try to sort out what that would be).
IF((MONTH(End_Date__c)) = MONTH ( TODAY() )), (Round(Day(End_Date__c)())*(Total_Line_Item_Streams__c / (( End_Date__c - Start_Date__c )+1)), IF(MONTH( My_StartDate__c ) = MONTH ( TODAY() )), ((Round(({Month's Final Date} -Start_Date__c ),0))*(Total_Line_Item_Streams__c / (( End_Date__c - Start_Date__c )+1)), IF (((today()>Start_Date__c)&&(today()<End_Date__c))), ({Days in this month}*(Total_Line_Item_Streams__c / (( End_Date__c - Start_Date__c )+1)), 0)
I can guaranteee you that this will require lots of cleaning and I will come back and clean it if I have time. Also, I left out a section of If statements that would input the appropriate amount of days in the current month
If ((MONTH ( TODAY() )=1), 31, If((MONTH ( TODAY() )=2), 28, etc.)
As well as a section that calculates the last day of the month (to compare against the start date) which you should be able to do with IF statements that input the last day of a given month and append it with the current year.
I will come back when I have some more time because I want to see what the solution looks like (even if I have to build it).
I would also suggest looking at "Examples of Advanced Formula Fields" in the Help & Training section of your Org. You should be able to Frankenstein across that code to make a solution.
Sorry I couldn't be more helpful (and realize that the example I provided needs considerable cleaning) at the moment but I think this is the approach you should take.
If it works on the report, I suppose it would be better there. Otherwise, add it as a Formula field on the Custom Object.
Don't thank me until after you try it. ;)
Alright, I have a formula that will compile for you. This should get you all set but I did not actually perform testing other than to make sure it compiled and calculated values. In theory, this should do the job but you need to check it out and see if it performs as expected. I will post the entire formula here and then explain what it is doing and then explain what you need to know about it moving forward.
IF(((MONTH(End_Date__c)) = (MONTH(TODAY()))&&((MONTH(Start_Date__c)) <> (MONTH(TODAY())))), (Round(Day(End_Date__c),0))*(Total_Line_Streams__c /((End_Date__c - Start_Date__c)+1)),
IF((MONTH(Start_Date__c)) = (MONTH(TODAY()))&&((MONTH(End_Date__c)) <> (MONTH(TODAY()))),
((Round(((IF(((MONTH(TODAY()))=1),(DATE(2009,1,31)),
IF(((MONTH(TODAY()))=2),(DATE(2009,2,28)),
IF(((MONTH(TODAY()))=3),(DATE(2009,3,31)),
IF(((MONTH(TODAY()))=4),(DATE(2009,4,30)),
IF(((MONTH(TODAY()))=5),(DATE(2009,5,31)),
IF(((MONTH(TODAY()))=6),(DATE(2009,6,30)),
IF(((MONTH(TODAY()))=7),(DATE(2009,7,31)),
IF(((MONTH(TODAY()))=8),(DATE(2009,8,31)),
IF(((MONTH(TODAY()))=9),(DATE(2009,9,30)),
IF(((MONTH(TODAY()))=10),(DATE(2009,10,31)),
IF(((MONTH(TODAY()))=11),(DATE(2009,11,30)),
IF(((MONTH(TODAY()))=12),(DATE(2009,12,31)),Today()))))))))))))) -(Start_Date__c +1)),0))*
( Total_Line_Streams__c / ((End_Date__c - Start_Date__c )+1))),
IF ((((MONTH(Start_Date__c)) = (MONTH(TODAY())) &&((MONTH(End_Date__c)) = (MONTH(TODAY()))))),
Total_Line_Item_Streams__c , 0)))
Ok, this formula breaks down into three portions. The first section (before the first space) evaluates if the End Date is in the current month but the Start Date is not. If that is the case, it simply multiplies the the Daily Stream Volume (I calculated it here rather than using your formulas, that way it's not dependent on the formulas in case you want to manipulate them later on and not affect this calculation) by the days in the current month (simply derived from the day portion of the End Date.
The second section flips the first and evaluates if the Start Date is in the Current month but the End Date is not. This is where it gets tricky.
This section subtracts the start date from a generic date value that equals the end date of the current month. So, if the current month is April, this determines that and builds 4/30/09 as a value and subtracts the Start Date from it. This gives the formula a number of days that can then be multiplied against the Daily Stream Volume.
I did build a formula that would have derived this value against the current year but it was much too big to compile and hit the Formula Governor. Therefore, YOU NEED TO UPDATE THESE VALUES COME JANUARY OF 2010. You simply replace "09" with "10" and, if it's a leap year, replace Feb's last day value with 29.
I'm pretty sure there is a more elegant method of calculating this value but I just took this approach because I knew I could get you up and running for the year. If anybody else knows a sleek formula for constructing a final date for the current month that does not require manual updating over time, please share it.
Finally, the final section evaluates if BOTH the Start Date and End Date are in the current month. If this true, it just displays the Total Line Item Streams.
Hopefully, this does the trick and your user base rejoices and takes you out for lunch.
If you want a pipelining sort of formula that looks the past the the current month, you probably have to build a series of formulas based off of this one by adding month values to the current month or saying something like the following:
IF((MONTH(End_Date__c)) = 4), Etc.
Keep in mind, you can always update formula fields temporarily. That way, you can take this formula and change it briefly to recalculate for a future month if you want to, say, look at July's projected delivery volume.
All of that being said, you should be able to roll this field up to the parent object. Once you do that, just output this to a report for the current month (it won't work for anything other than the current month) and you should have what you are looking for.
Let me know if things go awry.
Thanks for this!
Couple of things.
I created a Formula Field on the Ad Placement Line Item (Custom Object) that is linked to the Contract via Master Detail Lookup Relationship and pasted in the Formula. Obviously I could not add this as a Contract field becuse the formula will not allow for child relationships.
I built this as a reportable field on my Report and allowed SUM on that field. It works great! Only problem is it does not seem to take into consideration the Start Date as included in the Total Line Streams.
as you can see the Start Date is 4/6/2009 (so that day is included in the Formula(Current Months Bookable Business (Streams)))
However when you including 4/6 would mean that there is 25 days in the month of April.
The Formula you gave me is only calculating 24 days for the month of April.
I have edited the formula, copy it and try it again.
I basically replaced the start date field in the middle section with
(Start_Date__c +1)
If you find these sort of issues, I'm always happy to help. I would strongly recommend that you get aggressive at adapting this sort of code as well as it will be the best way to build your skillset. Use your Developer Account to have an org with Custom Objects that you can trial and error in. That is basically how I built this for you. I just made an object with your field names, took code from the community and relied on SFDC's syntax engine to tell me what it was looking for.Adjusted it to
(Start_Date__c -1)
And it worked correctly. Thanks.See, you're already a step ahead of me. ;)
Happy CRM'ing!