I needed to write a report to compare the target vs. actual total dollar amount of opportunities per month. First I created a formula on my basic opportunity report to summarize my Amount field. Then I used this case formula I found and it worked perfectly to create another column with my target amount:
CASE(FISCAL_YEAR:MAX,
2015,25000,
0)
This worked out great.
Next I need to write a report comparing the target vs. actual number of contracts signed per month. I thought I'd be able to use the same formula and just change the 25000 to our target number of contracts per month, but that's not working. Instead of creating a formula to summarize the amount field (since I just want to count the number of contracts), I added a formula to my basic contract report to do a record count and that worked fine to show my actual number of contracts per month. Now I'm trying to write the formula to show the target number of contracts per month and that's where I'm stuck. Here's what I've tried:
CASE(Record_Count:MAX,
2015,6,
0)
"Error: Invalid custom summary formula definition: Field RECORD_COUNT:MAX does not exist. Check spelling."
It doesn't recognize RECORD_COUNT as a field (which it isn't...it's just a formula on the report that's counting the records). FIrst I tried it with FISCAL_YEAR in there like in the original formula, but that didn't work either.
Does anyone know what I'm doing wrong or the correct way to go about this?
The reason your 'Target' is 0 is that the RowCount is 1 -- not 2015. You can set just a 6 in your formula to that Target is always 6.
