Different Workbook formula for date wise dataset filtering

I would like to remind everyone that the formula definition from the saved search is not fully compatible with the Dataset.

Examples (using Transaction base record on the dataset):

Amount This Year To Date 

CASE

 WHEN {trandate} <= CURRENT_DATE AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) 

THEN TO_NUMBER({foreigntotal})

END

Amount Last Year To Date 

CASE 

 WHEN ADD_MONTHS({trandate}, 12) < CURRENT_DATE AND EXTRACT(YEAR FROM {trandate}) + 1 = EXTRACT(YEAR FROM CURRENT_DATE)

 THEN TO_NUMBER({foreigntotal})

END

Current Year 

CASE WHEN EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN {foreigntotal} ELSE 0 END 

Last Year 

CASE WHEN EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)-1 THEN {foreigntotal} ELSE 0 END

Quarter To Date 

CASE WHEN TO_CHAR({trandate},’Q’) = TO_CHAR(CURRENT_DATE,’Q’) AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)

THEN {foreigntotal} 

END

Last QuarterTo Date 

CASE WHEN TO_CHAR({trandate},’Q’)-1 > 0 AND TO_CHAR({trandate},’Q’)-1 = TO_CHAR(CURRENT_DATE,’Q’)-1 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)

THEN {foreigntotal} 

ELSE

CASE WHEN TO_CHAR({trandate},’Q’)-1 = 0 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)-1

THEN {foreigntotal}

END

END

Month To Date 

CASE WHEN Extract(Month from {trandate}) = Extract(Month from CURRENT_DATE) AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)

THEN {foreigntotal} 

END

Last Month To Date 

CASE WHEN (ADD_MONTHS({trandate}, 1) <= CURRENT_DATE) AND (Extract(Month from ADD_MONTHS({trandate}, 1)) = Extract(Month from CURRENT_DATE))

THEN {foreigntotal} 

END 

First Day of this Month 

TRUNC(LAST_DAY(CURRENT_DATE)-1, ‘MONTH’)

Last Day of this month 

LAST_DAY(CURRENT_DATE)

First Day of last month 

TRUNC(LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1)), ‘MONTH’)

Last Day of last month 

LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1))

This Quarter

CASE WHEN TO_CHAR({trandate},’Q’) = TO_CHAR(CURRENT_DATE,’Q’) AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)

THEN TO_NUMBER({foreigntotal})

END

This Quarter Last Year to Date

CASE WHEN TO_CHAR({trandate},’Q’) = TO_CHAR(CURRENT_DATE,’Q’) AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)-1 AND ADD_MONTHS({trandate}, 12) <= CURRENT_DATE

THEN TO_NUMBER({foreigntotal})

END

Last Quarter

CASE WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,’Q’)) > 1 AND TO_NUMBER(TO_CHAR({trandate},’Q’)) = TO_NUMBER(TO_CHAR(CURRENT_DATE,’Q’))-1 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)

THEN TO_NUMBER({foreigntotal})

WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,’Q’)) = 1 AND TO_NUMBER(TO_CHAR({trandate},’Q’)) = 4 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)-1

THEN TO_NUMBER({foreigntotal})

END

Last Quarter Last Year

CASE WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,’Q’)) > 1 AND TO_NUMBER(TO_CHAR({trandate},’Q’)) = TO_NUMBER(TO_CHAR(CURRENT_DATE,’Q’))-1 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)-1

THEN TO_NUMBER({foreigntotal})

WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,’Q’)) = 1 AND TO_NUMBER(TO_CHAR({trandate},’Q’)) = 4 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)-2

THEN TO_NUMBER({foreigntotal})

END