Hi SuiteAnalytics friends,
since formulas are quite popular topic among the Dataset/Workbook user base I would like to share with you few useful ones. This will hopefully speed up the process of building new Workbooks. This time, we will look at the date and time manipulation and filtering.
I would like to remind everyone that the formula definition from saved search are not fully compatible with the Dataset, so sometimes it's not about copy&paste effort.
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))