https://bugs.documentfoundation.org/show_bug.cgi?id=161767
Bug ID: 161767
Summary: Improve handling of label row of dataranges for SORT,
SORTBY and FILTER functions
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 194934
--> https://bugs.documentfoundation.org/attachment.cgi?id=194934&action=edit
Example with SORT and FILTER function
The new functions SORT and SORTBY can use a database range as source array. If
the database range has labels, then this label row is sorted as if it is a data
row. That should not happen.
Assume a database range ´myData´ with a column label ´Sales´. Then a function
call
´=FILTER(myData; myData[Sales]<3000)´ is not possible because ´myData´ has one
row more than ´myData[Sales]´.
Excel has "format as table" as concept similar to database ranges. Using such
array in the SORT function in Excel does not include the label row in the
target array. There is an enhancement request for including it in
https://feedbackportal.microsoft.com/feedback/idea/2a55004c-1db0-ee11-92bd-6045bd7fe601
Currently you need to add the label row manually in Excel. If "Members" is the
table name, you can use the formula
´=VSTACK(Members[#Headers];SORT(Members[#Data];2))´, for example.
If there is no [] restriction, the reference ´Members´ means ´Members[#Data]´
in Excel. Formula ´=SORT(Members[#All];2)´ in Excel would result in the same
target array with sort in label row as current implementation in LibreOffice.
I see these ways to solve the problem:
(A)
Add an option like ´[#Data]´ to the database range reference to exclude the
label row from the target array, and if not used, keep the label row and
position it at the beginning.
(B)
Let the function always work on the array without labels and add an additional
parameter "add labels at top" to the functions, to add the source labels at top
of the target array after the data is sorted/filtered.
(C)
Let the function always work on the array without labels and add VSTACK and
HSTACK functions and a database range reference restriction like ´[#headers]´
to allow the user a solution like ´=VSTACK(Members[#Headers];SORT(Members;2))´
Any other ideas?
Keeping the label row _always_ at the beginning is no solution, because then a
combination like SORT(FILTER(...)) would not be possible because the result of
the inner FILTER function is a database range without name and thus the whole
array including the label row would be input to the outer SORT function.
The ODF TC has just started discussing the SORT, SORTBY and FILTER functions. A
solution that LibreOffice chooses could therefore be considered in ODF.
--
You are receiving this mail because:
You are the assignee for the bug.
