YTD calculation 1

[KathrynB]

Hello:

I have a report that is using the formula below to pull in data for a YTD field which is to coincide with the Cutoff date parameter value.


datevar svPriorYearEnd :=
date
(
iif(month({?Cut-off Date}) in [9 to 12],
year({?Cut-off Date}),
year({?Cut-off Date})-1),
8, 31
);

if tonumber({FA00902.FAYEAR}) > year(svPriorYearEnd) then
{FA00902.AMOUNT}
else
0


The problem is that the formula is pulling data beyond the cutoff date.
Is there an error in the formula or is there a better way that this can be written to get the proper data. I need to see the data as of the cutoff data that is entered and not beyond.

Thank you.

 

[lbass]

Your formula is only limiting the data to the fiscal year, not to data less than the cutoff date. You would have to have a date field that you could also build into the formula, something like:

if tonumber({FA00902.FAYEAR}) > year(svPriorYearEnd)
and {table.date} < {?Cut-off Date}+1
then {FA00902.AMOUNT}

-LB