Can I cheat the 'Sorting in a sub-query is not allowed' limitation?

I love the select on SUM option in e.g. a datawarehouse datasource. 
But when I use that with NL(Filter) and try to use the result in a NL(Rows) as a filter, I get the error 'Sorting in a sub-query is not allowed' 
Normally in cases like that I use the NP(Join) function to build a string with pipelines and use that as a filter in NL(Rows). 
But in this case it is impossible, because the string will exceed the maximum length Excel allows (32767 characters).

Is there any way to work around this problem?

Frankly I don't understand the error message, when it comes to use it as a filter. 
It is no problem to produce the keys in a NL(Rows), and NP(Join) is also ok, but using it as a filter in NL(Rows) can't be done.

I will add some example formula's to show what I have done so far.

G8: =NL("Rows";"Inventory Transactions";"Item Ledger Entry No.";"Document Type";"7..8";"Posting Date";NP("DateFilter";;"14-5-2018");"SUM(Outstanding Quantity)";"<>0") 
This produces perfectly a list of Item Ledger Entry No's 

But I don't want the list by Ledger entry, so I replaced 'Rows' by 'Filter' and added in G9 the formula 
=NL("Rows";"Inventory Transactions";"Document No";"Item Ledger Entry No.";G8) 
That goes wrong.

As far as I can see the field I use as the 3rd parameter in G8 is used as the grouping key to calculate the SUM on. Therefore I can't use the second formula with the SUM() clause, because it doesn't give me the expected result.

Any ideas?

Thanks in advance.


Please sign in to leave a comment.