Question using IIF...
I have an IIF statement which looks at the ending date of a customer's maintenance to see if they are 'current' or not.
For the most part, it works great. However there are customers who make more than one purchase in a year and may/may not purchase maintenance on one of those purchases. (1 purchase has maintenance and the other 2 do not).
The full query looks at sales for a specific year and groups them by geo, customer, customer size, and sums the invoice for that year. I then went and added the 'maintenance' field to see quickly who has maintenance or not.
Geo, Customer, Size Segment, SumofInvoice, Maintenance Status
When I remove the status field I get 1 line per customer, as expected. When I add the status flag it isn't consistent. Some customers who have made multiple purchases come out on one line and some don't. Sometimes I show 'Current' and 'Expired' for the same year.
Is it possible to return 'Current' for the year if one of their purchases are 'Current' for that year regardless of the other purchases?
I have an IIF statement which looks at the ending date of a customer's maintenance to see if they are 'current' or not.
Code:
Maintenance Status: IIf([Invoice Data]![Maint End:]<Date() Or [Invoice Data]![Maint End:] Is Null,"Expired","Current")The full query looks at sales for a specific year and groups them by geo, customer, customer size, and sums the invoice for that year. I then went and added the 'maintenance' field to see quickly who has maintenance or not.
Geo, Customer, Size Segment, SumofInvoice, Maintenance Status
When I remove the status field I get 1 line per customer, as expected. When I add the status flag it isn't consistent. Some customers who have made multiple purchases come out on one line and some don't. Sometimes I show 'Current' and 'Expired' for the same year.
Is it possible to return 'Current' for the year if one of their purchases are 'Current' for that year regardless of the other purchases?
Last edited:
