Print this page

How can I fix "#Error!" in Custom Report Formula that use the PREVGROUPVAL logic

Knowledge Article Number 000194034
Description Customer have a matrix report that compares previous values using PREVGROUPVAL. The report is good shape but customer is getting the #Error! flag due to some of the previous values = 0. How can this be fixed?
Resolution You can create a Custom Summary Formula using an "IF" and "AND" Conditions

IF(AND(logical1,logical2,...), value_if_true, value_if_false)

IF (
AND(PREVGROUPVAL(Order_Headers__c.Net_Sales__c:SUM, Order_Headers__c.Order_Date_Year__c) <> 0,
Order_Headers__c.Gross_Margin_Amount__c:SUM <> 0),
(Order_Headers__c.Net_Sales__c:SUM / PREVGROUPVAL(Order_Headers__c.Net_Sales__c:SUM, Order_Headers__c.Order_Date_Year__c) -1),

So whenever Previous value is not zero it will use the formula otherwise nothing will be displayed meaning the #Error! will not appear

For further information on how to fix the "#Error!" on reports please check the article How to remove #Error! values from custom Summary Formulas on reports?
for further information about the PREVGROUPVAL formula logic, please check the article PARENTGROUPVAL and PREVGROUPVAL

promote demote