Calculated Filter


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.

1 comment

Please sign in to leave a comment.