When generating reports, the necessity to contrast data between the current year and the previous year often arises, enabling a comprehensive assessment of performance trends and aiding in strategic decision-making. This comparison offers valuable insights into growth, patterns, and changes over time, contributing to a more informed understanding of the data’s significance.
Decode function can be used to extract the total amount from transactions that occurred during the same month last year. The Decode function is a conditional expression that evaluates a given value against multiple conditions and returns a result based on the condition that matches.
Create a Saved search with transaction type. Select the criteria field as needed.
Click on the Results tab. Opt for the Formula (Currency) and within the formula column, input the specified formula as mentioned.
DECODE(TO_CHAR({transactiondate}, ‘YYYY-MM’), TO_CHAR(ADD_MONTHS({transactiondate}, -12), ‘YYYY-MM’), {amount}, 0)
In this formula:
TO_CHARfunction converts the date into a string in the ‘YYYY-MM’ format.ADD_MONTHSfunction calculates the date offset by 12 months (1 year ago).DECODEfunction evaluates if the current month and year match the previous year’s month and year. If true, it returns the transaction amount; otherwise, it returns 0.