I have a report I’m building for all funded loans my organization has, and I have all the data needed for current year to date, quarter to date, and month to date. However, I’m not sure how to go about doing it.
I tried to create a variable as follows:
=RelativeDate(CurrentDate();-1;YearPeriod)
I know the formula itself is solid but I’m still not sure how to incorporate this variable. I’ve also tried adding that formula to existing formulas but I only get errors.
For example, I’ll use the loan count formula:
=Count([MTD].[Loan Number])
MTD = Date Funded is >= Start of current month
I tried to add the RelativeDate formula like so:
=RelativeDate(Count([MTD].[Loan Number]));-1;YearPeriod
and
=Count(RelativeDate([MTD].[Loan Number]);-1;YearPeriod)
The first formula gives me the error: The expression or sub-expression at position 40 is not valid. (IES 10036)
The second gives me this: The function ‘Count’ has missing arguments or closing parenthesis at position 39. (IES 10061)
I’m new to business objects and query language as a whole so maybe I’m missing something simple. Any help is greatly appreciated!
Date Group:
=If(MonthNumberOfYear([Date]) <= MonthNumberOfYear(CurrentDate()) And Year([Date]) = Year(CurrentDate());“YTD”;If(MonthNumberOfYear([Date]) <= MonthNumberOfYear(CurrentDate()) And Year([Date]) = Year(CurrentDate()) -1;“LYTD”))
Your [Date] should be the actual date attached to the loans. This logic will be flexible enough to flag Year To Date and Last Year To Date data in each group so you can =Count(If([Date Group] = “YTD”;[Loan Number])) or drop the data into blocks and filter by it to look at YTD in one and LYTD in another, etc.
This same type of logic should allow you to use months/quarters to create flags for your other to date type calculations.
I went ahead and added new lines for the MTD and QTD fields using that formula you gave me, and it works! Thank you so much for all your help, this solved my problem.
