CODEWITHSUNDEEP
reporting-servicesreportbuilder
Wildeman
Wildeman

Reputation: 1

I am not making the jump from IF-Then-Else to IIF in Report Builder

I am struggling with Report Builder and would appreciate so help. I want to find a percentage of ChargeOffAmount / LoanAmount based on a date.

IF(Fields!Chargeoffdate.Value is less than 2 years ago then Sum(Fields!ChargeOffAmount.Value) IF(Fields!Chargeoffdate.Value is more than 2 years ago or there is no date, then ignore the (Fields!ChargeOffAmount.Value)

Divided by

IF(Fields!Chargeoffdate.Value is less than 2 years ago then Sum(Fields!LoanAmount.Value) IF(Fields!Chargeoffdate.Value is more than 2 years ago or there is no date, then ignore the (Fields!LoanAmount.Value)

Equals the charge-off ratio

I am too embarrassed to show my failed efforts.

Best Regards

Upvotes: 0

Views: 53

Answers (1)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Not knowing the structure of your data nor the report, I guess that each ChargeOffAmount and each LoanAmount has a Chargeoffdate and you want to take into account only the values of the last 2 years.

Of Course, you could use a filter (or WHERE clause in your Dataset) to restrict the records to only the last 2 years, but I think that you are looking for something like this:

=Sum(IIf(Fields!Chargeoffdate.Value >= Today.AddYears(-2), Fields!ChargeOffAmount.Value, 0))/Sum(IIf(Fields!Chargeoffdate.Value >= Today.AddYears(-2), Fields!LoanAmount.Value, 0))

Upvotes: 1

Related Questions