Hi everyone,
We are trying to incorporate more Summary Level Metrics to our reporting and I'm less experienced with summary level formulas... it's easy in theory but I'm just unsure how to get the end result I need.
I have an Opportunities report bucketed by a date field (which is grouped by Calendar Month) and I want to create a field to show the average $ Amount from the previous 3 months.
For example:
February: Total of $50,000 among all opportunities
March: Total of $70,000
April: Total of $40,000
In May, I would like to see the average total from those previous 3 months in this summary formula field ($53,333) to compare to what the actual total $ Amount is.
Is this possible? Or any other ideas on how to display this data? I'm trying to avoid creating an additional Opportunity field
Thank you everyone!!
Like this?
Formula A =
(
BLANKVALUE(PREVGROUPVAL( AMOUNT:SUM , CLOSE_MONTH, 1), 0) +
BLANKVALUE(PREVGROUPVAL( AMOUNT:SUM , CLOSE_MONTH, 2), 0) +
BLANKVALUE(PREVGROUPVAL( AMOUNT:SUM , CLOSE_MONTH, 3), 0)
)
Formula C =
(
BLANKVALUE(PREVGROUPVAL( AMOUNT:SUM , CLOSE_MONTH, 1), 0) +
BLANKVALUE(PREVGROUPVAL( AMOUNT:SUM , CLOSE_MONTH, 2), 0) +
BLANKVALUE(PREVGROUPVAL( AMOUNT:SUM , CLOSE_MONTH, 3), 0)
)
/ 3
