i want to sum the quantities of the items of the previous year from jan to december of the previous year CASE WHEN {type} IN ('Invoice', 'Cash Sale') AND {trandate} BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE THEN {quantity} WHEN {type} IN ('Credit Memo', 'Cash Refund') AND {trandate} BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE THEN +{quantity} ELSE NULL END

AI generated answer (using internal Slack threads):

Based on the conversations, it seems like you are trying to create a formula that calculates the sum of quantities for specific transaction types within a certain date range. Here is a simplified version of your formula:

sql
CASE 
    WHEN {type} IN ('Invoice', 'Cash Sale') AND {trandate} BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE THEN {quantity}
    WHEN {type} IN ('Credit Memo', 'Cash Refund') AND {trandate} BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE THEN -{quantity}
    ELSE NULL
END

This formula checks the transaction type and date. If the transaction type is 'Invoice' or 'Cash Sale' and the transaction date is within the last 12 months, it sums the quantity. If the transaction type is 'Credit Memo' or 'Cash Refund' and the transaction date is within the last 12 months, it subtracts the quantity. For all other cases, it returns NULL. Remember to use this formula in the Results tab of your saved search, and set the Summary Type to 'Sum' to get the total quantity. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******