I’m trying to add three new columns into a report I’ve already created. I’d like to sum up the item quantities by their age. So, the first column would be the total quantity for each item for the last 0-7 days, second would be for the last 7-14 days and the last would be 14 days.

This is what I have currently:

=IIF(Fields!Date.Value >= DATEADD(DateInterval.Day, -7, FormatDateTime(Today)), SUM(Fields!Qty.Value))

I’ve tried modifying this in several ways but all (including the expression above) are underlined in red indicating that there is an error. I’ve tried:

= IIF(Fields!Date.Value >= DATEADD(DateInterval.Day, -7, Now()), SUM(Fields!Qty.Value))
= IIF(Format(Fields!Date.Value, “dd/mm/yyyy”) >= DATEADD(DateInterval.Day, -7, Format(Today(), “dd/mm/yyyy”)), SUM(Fields!Qty.Value))

I’m at a loss as I don’t really write expressions too often. Am I going about this the wrong way?

CodePudding user response：

The issue with your IIF is that there isn't an ELSE condition after the SUM. The IIF will return the value if the condition is true and the if not.

IIF(< condition >, < true >, < false >)

Also you want the sum to be outside the IIF in this instance, otherwise it will check the first date and if it matches add all the quantities from all dates.

=SUM(IIF(Fields!Date.Value >= TODAY.AddDays(-7), Fields!Qty.Value, 0))

If the Qty field is a decimal, the 0 may need conversion with CDEC(0).