i want to alter this function so that rather than it calculating of the past 12 months it calculates from the beginning of the year to today's date 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 provided, it seems like you want to modify your formula to calculate from the beginning of the year to today's date, and include 'Invoice', 'Cash Sale', 'Credit Memo', and 'Cash Refund' transaction types. Here's how you can modify your formula:

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

In this formula,

TRUNC(SYSDATE, 'YEAR')

will give you the date of the first day of the current year. The

SYSDATE

will give you the current date. So, this formula will calculate the quantity from the beginning of the year to today's date for the specified transaction types. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******