So I build a report that can show me of a billing document in SAP has been cancelled.
Now if that happens there should be a new billing document made.
What happens is the original billing gets cancelled and another flag field gets an “X”.
Also in that same process of cancelling there is an offsetting booking (and this billing) made without an “X” in the flag field.
These lines also belong to the same cancelled billing.
Finally the new correct billing is made and also these lines will have no “X” in the flag field.
However all these lines belong to the same Sales Order.
So if the sales order has 1 line and the billing of it will get cancelled and rebooked the outcome would be that the sales order will have 3 lines. 1 with an X and 2 without.
Now if I would filter on the flag field to see only sales orders which have had cancellations I would miss a lot of detail lines because only 1/3 of the lines that belong to these sales orders will be shown.
I am trying to pull this off with this formula (tried in different variations):
=If([Billing Document is cancelled]=“X”) In([Sales Ord.]) Then “Show” Else “Hide”
Could somebody put me on the right track as this is obviously not working?
Thanks @Bo_Bozo but I really need it as a filter not a conditional format.
Also I do not see how that solution would work for my problem.
(but that could my fault )
Maybe I missed it, I’ll try again.
Filter out anything that has an 'X"
Then you are left with some records duped, and some may not be correct?
If so, then Identify the ‘dupes’ with:
Then filter out the records that = 1. This should leave any record that is needed?
Well that is the problem.
"Filter out anything that has an “X” "
If this is what I needed than there is a specific field for this called “[Billing Document is cancelled]”
So if I filter on X then all other lines will be gone.
But I also need lines without the X.
Maybe a specific example.
I have sales order 12345 and this has a billing document ABC.
Now when the billing gets cancelled an offset billing document will be created DEF.
Then ABC line will get the “X” but line with DEF will get NO “X”.
And that the new billing document will get created GHI and this line will also have NO “X”.
So then Sales Order 12345 will have 3 lines. 1 with X and 2 without.
In the whole I will have of course a lot of Sales Order in my report and most of them will have no “X”.
I want to filter the Sales Order which have had a cancelled billing and thus an “X”.
If I filter on that field that hold the X I will get :
Sales Order-----Billing-----Cancel
12345--------------ABC------X
But I need :
Sales Order-----Billing-----Cancel
12345--------------ABC-------X
12345--------------DEF-------
12345--------------GHI--------
So I somehow need to filter on X WITHIN Sales Order.
If Sales order has somewhere an X then I need to show that line.
I think a subquery would work in this situation. Here is an example where I am working with Accounts and Transactions which would correspond to your Sales Order and Line Items.
So in my case I am looking for Account Numbers and all of their Transactions with a Financial Trans Date = “03/05/2021” regardless of their Financial Trans Code where there was at least one transaction with a Financial Trans Date = “03/05/2021” and Financial Trans Code = “790” or " 990". My Financial Trans Code is equivalent to your Cancel dimension.
So I get Transactions with Financial Trans Codes other than 790 and 990, but the Account has to have at least one of those to even be returned by the query.
Here is a video demonstrating this approach using data from the eFashion universe.
Interesting approach @nscheaffer .
But I am not certain if my request would need a subquery.
My Sales Order would indeed correspond with your account.
But for me there is only 1 parameter not 2 like you have.
In your example I would need all the accounts with transcode X for all trans dates.
So also if that account would have no transcode (blank and no X) for a trans date I still would need to see this line.
I would not need to see the accounts with transcode blank (no X) on all trans dates.
But if an account on any transdate holds a transcode of X then I would need to see all those lines of that account.
With some extra help from @Bo_Bozo we got this to work.
Key was to make values of the “X” ‘occurences’.
So with this one every line that had an X would get a value :
Canc.Count =If ([Billing Document is cancelled] = “X”;1;0)
And then with this one all lines IN a Sales Order that has somewhere an X will get a value too:
Keeper =Max([Canc.Count]) In ([Sales Ord.])
In this case all lines from a Sales Order that had somewhere an X will get the value 1 (also when the line itself had no X).
So with filtering on Keeper everything >= 1 is showing only the lines that I need.
)
