Hello
I would like to create a list of all the articles, that have a negative contribution margin in a specific period of time.
I am aware, that there is an easy possibility to generate a list with all sold articles and use a conditional hide to hide all articles with a negative contribution margin. But I need a list without hidden rows.
For my NL("rows","Value Entries","No.") I will need a calculated filter, which is easy done (sorry if I don't name the fields correctly, I work with a german version):
- NL("rows","Value Entries","No.";"-=NF(,""Sales Value"")+NF(,""Cost Value"")","NUMBER&<0)
So far this works, but I would like to use some filters within the calculated filter field. For example I would like to use a date filter for the calculation. My first suggestion would look like this:
- NL("rows","Value Entries","No.";"-=NF(,""Sales Value"",""Posting Date"";""01.01.19..30.06.19"")+NF(,""Cost Value"";""Posting Date"";""01.01.19..30.06.19"")","NUMBER&<0)
This formula still works as it should. But I need several filters for my calculated filter field and Excel allows only 256 charcters in a fomula. Thats why I must use FILTERS= to use less characters. This would look like this:
- NL("rows","Value Entries","No.";"-=NF(,""Sales Value"",""FILTERS="",$B$2:$C$6)+NF(,""Cost Value"";""FILTERS="",$B$2:$C$6)","NUMBER&<0)
This formula is no longer working. Is there another way to solve my problem?
Thank you very much for your help.
