Hi.
I request your support for the creation of a custom indicator.
Today we use the "Dashboard" portlet where we have some custom indicators, this portlet has the date ranges of "January to September of this year" and "January to September of last year".
We want to make a personalized indicator that shows the difference in the balance that exists in an accounting account between January and September of this year and the previous year (These dates correspond to the range of dates that we have in the dashboard).
Example:
-January until September of this year
The balance that was in the account until January of this year is $ 953,517.04 and the balance that was in the account until September of this year is $ 486,972.19 and the difference between these two amounts is $ 466,544.85, this last amount is the one that you want to show in the column of the interval from "January to September
of this year "
-January until September of the last year
The balance that was in the account until January of the last year is $ 0 and the balance that was in the account until September of the last year is $ 1,002,044.94 and the difference between these two amounts is $ -1,002,044.94, this last amount is the one that you want to show in the column of the interval "January to September of the last year "
To do this, a saved search is being created in which in the results tab with a numerical formula field the following formula is being placed:
(CASE WHEN {trandate} BETWEEN TO_DATE ('01 / 01/2020 ',' DD / MM / YYYY ') AND TO_DATE (TRUNC (ADD_MONTHS ({today}, - 8),' MONTH ')) THEN {amount} ELSE 0 END) - (CASE WHEN {trandate} BETWEEN TO_DATE ('01 / 01/2020 ',' DD / MM / YYYY ') AND TO_DATE (TRUNC (ADD_MONTHS ({today}, 0),' MONTH ')) THEN { amount} ELSE 0 END)
It is putting 01/01/2020 since it is the date from where the accounting of the transactions within NetSuite began to be made.
By putting this formula only the difference of the first column is obtained while the other one stays at 0.
Is there a way to make the correct direction display in each of the columns?
I hope you can help me.
Thanks!