So I’m working on a BAQ that shows Vendor Payable totals for the year.
For a handful of vendors, I need to omit Debit Memos.
I have an iif AND statement that does that, but when I try to group by vendornum, I get the following error:
How do I get around this? I’d like to have vendnum and a total as end result.
First, welcome to the forum. Second, if you could fix your screen shot so it’s more readable that would be really helpful. I recommend GreenShot as a screen capture program that makes it really easy to get screen shots.
To answer your other questions, instead of filtering in the calculated field, it would probably be easier to just filter out the rows in the table to not include them. Same for your vendor numbers. You do that in the design canvas. Then you can simple do a sum(yourfield) and get aggregate data without all of the other stuff.
Another way to maybe fix it, without changing it, it to wrap everything you have there in a sum(yourstuff).
You don’t actually have an aggregate function.
Thanks for the suggestions, I must admit I hadn’t thought of doing some of the filtering on the front side, I think I can work with that…
For the sake of completeness, I’ll add that when I tried to wrap the statement in a sum, I get this error:
Thanks for your help!
It looks like it doesn’t like that you aren’t including those fields in the display to group by. If you’re filtering on the front side doesn’t work (or you would just rather not do it that way), you can make your query a sub query, then do the summing/grouping on the top level.
Thanks, Brandon, I think the first suggestion will work perfectly for me…