Summary Formula Field Help - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Adam RycroftAdam Rycroft 

Summary Formula Field Help

Hi,

I'm trying to create a summary formula field on a currency field. The report is grouped by calendar week and I want to show how much the field approved amount is by adding up the previous totals as well. Example. If week 1 total is 1,000 and week 2's total is 1,000, i want to show total to date being 2,000.)

How can this be done? I've attached an image of what the report looks like now.summary formulas
Best Answer chosen by Adam Rycroft
Mikey BrownMikey Brown
Hi Adam,

Steve Molis did a great job outlining how you can build a cumulative formula here (https://trailblazers.salesforce.com/answers?id=9063A000000t4XMQAY) by using a combination of the BLANKVALUE and PREVGROUPVAL functions. 

Your formula should look something like this:
AMOUNT:SUM +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 1), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 2), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 3), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 4), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 5), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 6), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 7), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 8), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 9), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 10), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 11), 0)

Bascially, the Increment in the PREVGROUPVAL function says how far back to go, with each increment representing 1 week with the way you've structured the report. You'll just need to update how far out you go depending on how many weeks are included in your report. In the example above, this would cover you for 12 weeks of data. 

Thanks,
Mikey

All Answers

Mikey BrownMikey Brown
Hi Adam,

Steve Molis did a great job outlining how you can build a cumulative formula here (https://trailblazers.salesforce.com/answers?id=9063A000000t4XMQAY) by using a combination of the BLANKVALUE and PREVGROUPVAL functions. 

Your formula should look something like this:
AMOUNT:SUM +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 1), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 2), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 3), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 4), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 5), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 6), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 7), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 8), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 9), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 10), 0) +
BLANKVALUE(PREVGROUPVAL(APPROVED_AMOUNT:SUM , DATE, 11), 0)

Bascially, the Increment in the PREVGROUPVAL function says how far back to go, with each increment representing 1 week with the way you've structured the report. You'll just need to update how far out you go depending on how many weeks are included in your report. In the example above, this would cover you for 12 weeks of data. 

Thanks,
Mikey
This was selected as the best answer
Adam RycroftAdam Rycroft
Thanks Mikey. This helped me get most of the way there, but I'm stuck on house the date field should be written. I figured out that since the approved amount field is a custom field in a custom object it needs to be written like below, but I can't figure out any option for the date field that doesn't cause a syntax error. My report type is Accounts with In-House Grants. 

In_House_Grant__c.Approved_Amount__c:SUM +
BLANKVALUE(PREVGROUPVAL(In_House_Grant__c.Approved_Amount__c:SUM , DATE, 1), 0)
Adam RycroftAdam Rycroft
I figured out my problem - I was trying to display at the wrong grouping.
Mikey BrownMikey Brown
Awesome! I'm glad you were able to get it working!