Summing Transaction Amounts Fiscal Year to Date

[agorjest]

Hello,
I’m Using Crystal 2013 on Windows 10 with an Access/Excel (DAO) connection to an Excel spreadsheet as the data source. (Need Excel Data as the source for this project, normally not optimal, I know). I’m having issues with a formula dependent on other formulas accurately summing $ amounts at the group header level.
The sum formula is trying to get the Year-to-Date total transaction amount for the account group. (Year to Date is based on a fiscal year 7/1 to 6/30, which is entered as a parameter by the user, along with the Month of the transaction)

Formula: @AMOUNT YTD:
if {TRANS_CSV.TYPE} = "TRANSACTION" and {@PERIOD-FY} = to_number({?Current Fiscal Year}) then {TRANS_CSV.AMOUNT}

Dependent formula @PERIOD-FY:
Evaluateafter ({@PERIOD-MONTH});
Evaluateafter ({@PERIOD-YEAR});
If {@PERIOD-MONTH} in ['JUL','AUG','SEP','OCT','NOV','DEC'] then {@PERIOD-YEAR} + 1
else {@PERIOD-YEAR}

Dependent formula @PERIOD-MONTH:
whilereadingrecords;
ucase(left({TRANS_CSV.PERIOD},3))

Dependent formula @PERIOD-YEAR:
whilereadingrecords;
IF isnumeric(right(TRANS_CSV.PERIOD},4)) THEN tonumber(right({transactions_csv.Period},4))

Note: The field TRANS_CSV.PERIOD represents a month and year but is a string displayed as MMM-YYYY (i.e. JAN-2018) in the Excel data source. There is not ‘transaction date’ field.

The above formulas are all working correctly and a sum of @AMOUNT YTD is placed among many other sums in the Group header, but it appears all of the transactions of the last full calendar year are being included in the sum, when I only want the ones from the beginning of the fiscal year included. For instance, if the user-chosen parameters are Month=FEB and FY =2019, then all transactions dating back to February of 2018 are summed, when I only want the ones from July 2018 to February 2019 included.

It may have something to do with the sequence of when the formulas pass through CR, or perhaps I’m not doing this the right way? Any help would be much appreciated.
Thank you in advance.

 

[lbass]

What is your selection formula? I can’t see where you are using the month parameter.

-LB

 

[agorjest]

There is no selection formula, the report is pulling all the data from the spreadsheet. The month parameter is being used in some period-to-date formulas, but not in this FY-to-date formula. Should it be incorporated into calculating for the fiscal YTD?

 

[lbass]

Your {@Amount-YTD} formula only specifies fiscal year, so I was wondering how you were limiting the months, which would be difficult with an alpha month.

I think you would be better off starting by converting your fields to a date, as in:

//{@cdt}:
stringvar x := {TRANS_CSV.PERIOD};
numbervar y := (select ucase(left(x,3))
case "JAN" : 1
case "JUN" : 6 //add in other months
case "JUL" : 7
case "OCT" : 10
default : 0
);
datevar dt := if isnumeric(right(x,4) then
date(tonumber(right(x,4)),y,1) else
date(0,0,0);

Then change {@Amount-YTD} to:

datevar dt:
if {TRANS_CSV.TYPE} = "TRANSACTION" and
{@cdt}<>date(0,0,0) and
year({@cdt}+184) = to_number({?Current Fiscal Year}) and //instead you could just make the parm a number parm and remove the tonumber()
{@cdt}<= date({?Current Fiscal Year},{?Month},1) then //assuming the month parm identifies the ending month of the range
{TRANS_CSV.AMOUNT}

-LB