function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Amber NeillAmber Neill 

counting and calculating within date ranges

I'm trying to write a report to calculate activities that happen within a date range.
I have "stopdate" and "startdate" as fields on my Accounts object and I want to do math on those counts.  Here's what I have so far:

last month:   RowCount((MONTH(startdate) = (MONTH(TODAY())-1)) AND ISPICKVAL(Type ,"Full Customer"))
not last month:   RowCount((MONTH(startdate) <> (MONTH(TODAY())-1)) AND ISPICKVAL(Type ,"Full Customer"))

Then I want to do a percentage of total calculation on this: "last month"/ ("last month" + "not last month")

Two problems.

1.) I get a syntax error b/c it says these fields don't exist.  I'm copying the field names from the Fields list on the Accounts object, so I have no idea what it is expecting.
2.) Is my formula workable or am I barking up the wrong tree?

Thanks for any and all advice!
Amber
Steve :-/Steve :-/
Just a guess, but I wonder if it is bombing because it is expecting a fields named (Account)last month and (Account)notlast month


Do you have custom formula fields on the Account record for (Account)last month and (Account)notlast month that you are evaluating?


Message Edited by Stevemo on 01-19-2009 02:57 PM
Amber NeillAmber Neill
Nope, I'm hoping to have it do the math for me.  I REALLY don't want to have to re-write the formula everytime I run the report!
:-)
Amber
Steve :-/Steve :-/
Good News*


(* maybe)

I think you can leverage the code in your report to build a formula field on your Account object.

Code:
last month:   RowCount((MONTH(startdate) = (MONTH(TODAY())-1)) AND ISPICKVAL(Type ,"Full Customer"))


not last month:   RowCount((MONTH(startdate) <> (MONTH(TODAY())-1)) AND ISPICKVAL(Type ,"Full Customer"))
If you create to custom formula fields that uses your code to evaluate the StartDate field, and Account.Type field, you *should* be able to run a report that evaluates the count of Last Month (TRUE) vs. Last Month (FALSE)  and gives you a number that you can use to get your Last Month %
 
Amber NeillAmber Neill
As a field update or a workflow rule?
Amber
Steve :-/Steve :-/
I think a straight Formula Field oughta do it (maybe I'm missing something).
Amber NeillAmber Neill
Yeah, that'd work, but I want to track month over month.  I could do an analytic snapshot...but then I can't do any back data calculations (only going forward).  What if I created a custom object that tracked this and updated it each month?  yuck!  There has to be a way to do this by doing date math!  :(


Message Edited by Amber Neill on 01-19-2009 06:09 PM
Steve :-/Steve :-/
I think a custom object related to the account might be the way to go. 

Otherwise you may also be able to use the Account Field History to track the values in your Account.StartDate and Account.EndDate fields over time (but that's just a guess).
Steve :-/Steve :-/
I just did a test on my Dev.Org, and I could run a report on Account Field History, so that might be an option for you after all.