How to Compare Transaction Amounts Across Two Date Ranges in NetSuite

To compare transaction amounts across two separate date ranges in side-by-side columns within a NetSuite transaction saved search, apply the following formulas. This method effectively simulates KPI current/previous comparisons.Saved Search Criteria:Set the criteria to include dates within both the current and last fiscal year-to-date. Use expressions and parentheses for the OR condition:Date is within this fiscal year to date OR last fiscal year to dateFormula Fields:Current Fiscal YTD formula:decode(to_char(add_months({trandate},6),'YYYY'),to _char(add_months({today},6),'YYYY'),{amount},0)Previous Fiscal YTD formula:decode(to_char(add_months({trandate},-6),'YYYY'),to_char(add_months({today},-6),'YYYY'),{amount},0)How the Formulas Work (from innermost to outermost functions):* add_months({today},6): This function compensates the calendar year for a fiscal year that begins in July. To get the last fiscal year, use -6. Apply the same logic to {trandate}. If your fiscal year aligns with the calendar year, this step can be ignored.* to_char(adjusteddate,'YYYY'): This extracts only the (fiscal) year from the adjusted date.* decode(value1, value2, {amount}, 0): This compares the transaction date's fiscal year (value1) to the current or previous fiscal year (value2). If they are equal, the transaction {amount} is used; otherwise, it is zeroed out.