[Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

classic Classic list List threaded Threaded
9 messages Options
Kohei Yoshida-5 Kohei Yoshida-5
Reply | Threaded
Open this post in threaded view
|

[Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

Hi there,

I'd like to merge Calc's label range functionality (which you can find
in the menu at Insert - Names - Labels) with named range functionality.
The named range functionality is found at Insert - Names -
Define/Manage/Insert. Also, the Create menu item in the same menu
sub-tree is also a part of the named range functionality.Here, what I
basically mean by "merging" is to remove this functionality and provide
some rudimentary mapping to the named range when importing legacy
documents that use this functionality.

What the label ranges provide can be pretty much achieved by named
ranges, and with named ranges you can do much much more. The UI for
label ranges is very clunky, unattractive, and hard to use.  Rather than
trying to maintain and improve this almost duplicated functionality, we
should just drop it and encourage users to use named ranges instead.  
Dropping this functionality would also help clean up the implementation
and make it easier for us to improve the formula engine going forward.
So, I would passionately love to see that happensince the presence of
this functionality currently is making it harder to maintain the formula
engine code, much less improve it.

Excel also had similar functionality but dropped it in Excel 2007, and
steered the users toward using named ranges. So, there is no concerns
with regard to interoperabilityeither.

Best,

Kohei

--
Kohei Yoshida, LibreOffice Calc hacker, SUSE.

_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise
Eike Rathke-2 Eike Rathke-2
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

Hi Kohei,

On Wednesday, 2013-07-17 20:29:06 -0400, Kohei Yoshida wrote:

> I'd like to merge Calc's label range functionality (which you can
> find in the menu at Insert - Names - Labels) with named range
> functionality. The named range functionality is found at Insert -
> Names - Define/Manage/Insert. Also, the Create menu item in the same
> menu sub-tree is also a part of the named range functionality.Here,
> what I basically mean by "merging" is to remove this functionality
> and provide some rudimentary mapping to the named range when
> importing legacy documents that use this functionality.

As I see it the Label functionality currently can't be replaced by named
expressions (ranges):

* The actual label name displayed is taken from a cell's content,
  formula expressions using a label automatically change their display
  label names whenever that cell content is changed.
  * This is not possible with named ranges.

* One label names exactly one row or one column, expressions or
  multi-column/row ranges are not possible.
  * The named expressions dialog could restrict that though.

* The label name can include spaces and other arbitrary characters that
  in a formula expression would have special meanings, using such a name
  in an expression is possible by enclosing the entire label name in
  single quotes. A label name can even be a string that otherwise would
  be a cell reference.
  * A named range currently has to consist of alphanumeric+underscore
    characters and can't resemble a cell reference.
  * ODFF does provide means to store usage of such non-simple names
    though with $$SingleQuoted but we need to implement that in the
    formula compiler (anyway), see
    http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1017964_715980110

Furthermore we probably could use exactly the Label functionality for
the GSoC "Enhanced Database Ranges" Table feature when it comes to
in-Table formula expressions adressing the Table's rows or columns.
Actually it would be necessary to support identical label names for
different Tables (ranges) within one sheet, again this is not possible
with named ranges.

  Eike

--
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
For key transition see http://erack.de/key-transition-2013-01-10.txt.asc
Support the FSFE, care about Free Software! https://fsfe.org/support/?erack

_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise

attachment0 (853 bytes) Download Attachment
Kohei Yoshida-5 Kohei Yoshida-5
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

On 07/18/2013 08:09 AM, Eike Rathke wrote:

> Hi Kohei,
>
> On Wednesday, 2013-07-17 20:29:06 -0400, Kohei Yoshida wrote:
>
>> I'd like to merge Calc's label range functionality (which you can
>> find in the menu at Insert - Names - Labels) with named range
>> functionality. The named range functionality is found at Insert -
>> Names - Define/Manage/Insert. Also, the Create menu item in the same
>> menu sub-tree is also a part of the named range functionality.Here,
>> what I basically mean by "merging" is to remove this functionality
>> and provide some rudimentary mapping to the named range when
>> importing legacy documents that use this functionality.
> As I see it the Label functionality currently can't be replaced by named
> expressions (ranges):
>
> * The actual label name displayed is taken from a cell's content,
>    formula expressions using a label automatically change their display
>    label names whenever that cell content is changed.
>    * This is not possible with named ranges.
Sure. But is this *that* important to users?  To me the whole label
range implementation is such a duplicate functionality for very little
marginal difference, and I'm not really sure if that difference even
matters.

>
> * One label names exactly one row or one column, expressions or
>    multi-column/row ranges are not possible.
>    * The named expressions dialog could restrict that though.

I don't see how that restriction could be useful.  You can define one
column / one row only named ranges (or database ranges for that
matter).  Is there a use case where having this restriction is useful in
real life?

>
> * The label name can include spaces and other arbitrary characters that
>    in a formula expression would have special meanings, using such a name
>    in an expression is possible by enclosing the entire label name in
>    single quotes. A label name can even be a string that otherwise would
>    be a cell reference.
Yes.  And the fact that this can be a string is actually very scary to
me.  This potentially makes tracking references very difficult without
sacrificing performance.  Dropping it would enable us to optimize it
further.

>    * A named range currently has to consist of alphanumeric+underscore
>      characters and can't resemble a cell reference.
>    * ODFF does provide means to store usage of such non-simple names
>      though with $$SingleQuoted but we need to implement that in the
>      formula compiler (anyway), see
>      http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1017964_715980110
>
> Furthermore we probably could use exactly the Label functionality for
> the GSoC "Enhanced Database Ranges" Table feature when it comes to
> in-Table formula expressions adressing the Table's rows or columns.
> Actually it would be necessary to support identical label names for
> different Tables (ranges) within one sheet, again this is not possible
> with named ranges.

I'd rather we extend the database range code to support these missing
bits rather than piggybacking on top of the label range code.  I don't
see it as a reason why we need to keep label range.

Kohei



-- Kohei Yoshida, LibreOffice Calc hacker, SUSE.
_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise
Eike Rathke-2 Eike Rathke-2
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

Hi Kohei,

On Thursday, 2013-07-18 08:25:45 -0400, Kohei Yoshida wrote:

> >* The actual label name displayed is taken from a cell's content,
> >   formula expressions using a label automatically change their display
> >   label names whenever that cell content is changed.
> >   * This is not possible with named ranges.
> Sure. But is this *that* important to users?  To me the whole label
> range implementation is such a duplicate functionality for very
> little marginal difference, and I'm not really sure if that
> difference even matters.

For those who use it it probably is important ... anyhow, this is even
part of ODFF, so we somehow should support it. What is debatable is the
"automatic label lookup" that IMHO should be deprecated and the default
configuration setting be disabled.


> >* One label names exactly one row or one column, expressions or
> >   multi-column/row ranges are not possible.
> >   * The named expressions dialog could restrict that though.
>
> I don't see how that restriction could be useful.  You can define
> one column / one row only named ranges (or database ranges for that
> matter).  Is there a use case where having this restriction is
> useful in real life?

It is needed for the intersection of row and column labels, that works
only with vectors, e.g.  ='Sales' 'Hamburg'


> >* The label name can include spaces and other arbitrary characters that
> >   in a formula expression would have special meanings, using such a name
> >   in an expression is possible by enclosing the entire label name in
> >   single quotes. A label name can even be a string that otherwise would
> >   be a cell reference.
> Yes.  And the fact that this can be a string is actually very scary
> to me.  This potentially makes tracking references very difficult
> without sacrificing performance.  Dropping it would enable us to
> optimize it further.

The performance bottleneck is the automatic label thing where the
sheet's content is searched for a string; searching just a few defined
label ranges (if any) doesn't make much difference compared to named
ranges.


> >   * A named range currently has to consist of alphanumeric+underscore
> >     characters and can't resemble a cell reference.
> >   * ODFF does provide means to store usage of such non-simple names
> >     though with $$SingleQuoted but we need to implement that in the
> >     formula compiler (anyway), see
> >     http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1017964_715980110
> >
> >Furthermore we probably could use exactly the Label functionality for
> >the GSoC "Enhanced Database Ranges" Table feature when it comes to
> >in-Table formula expressions adressing the Table's rows or columns.
> >Actually it would be necessary to support identical label names for
> >different Tables (ranges) within one sheet, again this is not possible
> >with named ranges.
>
> I'd rather we extend the database range code to support these
> missing bits rather than piggybacking on top of the label range
> code.  I don't see it as a reason why we need to keep label range.
I meant the special Excel cell formula syntax for formulas in cells of
a Table that address rows/columns/intersections of the Table by their
header names. That is very similar to defined labels
compiler/interpreter-wise. Of course it doesn't matter where we actually
stick the "defined label" in, having them as part of the database range
probably is best because we usually can derive it from the top-row of
the database range (don't know currently if Excel allows more than one
row for those Table labels, they did a very awkward thing with their
labels back then).

  Eike

--
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
For key transition see http://erack.de/key-transition-2013-01-10.txt.asc
Support the FSFE, care about Free Software! https://fsfe.org/support/?erack

_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise

attachment0 (853 bytes) Download Attachment
Kohei Yoshida-5 Kohei Yoshida-5
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

On 07/23/2013 07:00 AM, Eike Rathke wrote:

> Hi Kohei,
>
> On Thursday, 2013-07-18 08:25:45 -0400, Kohei Yoshida wrote:
>
>>> * The actual label name displayed is taken from a cell's content,
>>>    formula expressions using a label automatically change their display
>>>    label names whenever that cell content is changed.
>>>    * This is not possible with named ranges.
>> Sure. But is this *that* important to users?  To me the whole label
>> range implementation is such a duplicate functionality for very
>> little marginal difference, and I'm not really sure if that
>> difference even matters.
> For those who use it it probably is important ...
And the original goal of my post was to figure out whether there are
people who use it, and if yes, what proportion of users (roughly), and
whether or not their use cases could be possibly fulfilled by either
named ranges or database ranges.  Somehow I'm not getting any feedback
on that front.

> anyhow, this is even
> part of ODFF, so we somehow should support it.
We should somehow *handle* it during import, yes. Presence of it in the
ODF spec does not automatically dictate that we should have the feature
available in the UI, for run-time use.

> What is debatable is the
> "automatic label lookup" that IMHO should be deprecated and the default
> configuration setting be disabled.
OK.

>
>
>>> * One label names exactly one row or one column, expressions or
>>>    multi-column/row ranges are not possible.
>>>    * The named expressions dialog could restrict that though.
>> I don't see how that restriction could be useful.  You can define
>> one column / one row only named ranges (or database ranges for that
>> matter).  Is there a use case where having this restriction is
>> useful in real life?
> It is needed for the intersection of row and column labels, that works
> only with vectors, e.g.  ='Sales' 'Hamburg'
Understood.  But again, you are talking about implementation details
here.  I was hoping for an argument that's more usability-oriented,
hence my motivation to CC it to ux-advise, and the emphasis on "use case
in real life".

>
>
>>> * The label name can include spaces and other arbitrary characters that
>>>    in a formula expression would have special meanings, using such a name
>>>    in an expression is possible by enclosing the entire label name in
>>>    single quotes. A label name can even be a string that otherwise would
>>>    be a cell reference.
>> Yes.  And the fact that this can be a string is actually very scary
>> to me.  This potentially makes tracking references very difficult
>> without sacrificing performance.  Dropping it would enable us to
>> optimize it further.
> The performance bottleneck is the automatic label thing where the
> sheet's content is searched for a string; searching just a few defined
> label ranges (if any) doesn't make much difference compared to named
> ranges.
*If* there are only a few defined label ranges used per document. Such
assumption (or hope) can be very fragile in reality and I tend not to
make such assumption especially when spreadsheet documents tend to
become very large very easily.

>
>
>>>    * A named range currently has to consist of alphanumeric+underscore
>>>      characters and can't resemble a cell reference.
>>>    * ODFF does provide means to store usage of such non-simple names
>>>      though with $$SingleQuoted but we need to implement that in the
>>>      formula compiler (anyway), see
>>>      http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1017964_715980110
>>>
>>> Furthermore we probably could use exactly the Label functionality for
>>> the GSoC "Enhanced Database Ranges" Table feature when it comes to
>>> in-Table formula expressions adressing the Table's rows or columns.
>>> Actually it would be necessary to support identical label names for
>>> different Tables (ranges) within one sheet, again this is not possible
>>> with named ranges.
>> I'd rather we extend the database range code to support these
>> missing bits rather than piggybacking on top of the label range
>> code.  I don't see it as a reason why we need to keep label range.
> I meant the special Excel cell formula syntax for formulas in cells of
> a Table that address rows/columns/intersections of the Table by their
> header names. That is very similar to defined labels
> compiler/interpreter-wise.
Sure. But this is an implementation detail that's not the focus of my
original post.  Whether or not we could re-use the label range
implementation for the enhanced database range feature is a topic for
another discussion, and one that doesn't have to involve our UX experts.

> Of course it doesn't matter where we actually
> stick the "defined label" in, having them as part of the database range
> probably is best because we usually can derive it from the top-row of
> the database range (don't know currently if Excel allows more than one
> row for those Table labels, they did a very awkward thing with their
> labels back then).
Noted.  Perhaps it was that awkward-ness that prompted them to drop this
feature in 2007?  I'm just guessing.

Kohei

--
Kohei Yoshida, LibreOffice Calc hacker, SUSE.

_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise
pierre-yves samyn pierre-yves samyn
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

Hello All

> Message du 23/07/13 19:46
> And the original goal of my post was to figure out whether there are
> people who use it, and if yes, what proportion of users (roughly), and
> whether or not their use cases could be possibly fulfilled by either
> named ranges or database ranges. Somehow I'm not getting any feedback
> on that front.


I do user support and training for (thirty) years.
I've never met one user of this feature.

However, and this goes against the opinions already given,
I found that the "automatic find label" option *is* used.

Two concrete use cases (where the find label option has an advantage
over "named ranges"):

1. Managing the expansion of the range.

The formula =SUM('Sales'), where 'Sales' is the header column,
will update if you add amounts. To achieve the same result with
a "named range" you must either use a "dynamic" name (calculated)
or plan ahead more than is actually filled range.

2. Adaptation of References

A table with two columns: "purchases" (column A) and "sales" (column B)
C1 =SUM('Purchase')
Copy C1 to D1 provides automatically =SUM('Sales') in D1

This can not be obtained directly if "Purchase" is a named range
(and if the "find label option" is deleted/disabled).

Do not get me wrong: I do not defend at all costs to maintain
this feature. I only see it is used and benefits.

Thank Kohei for all its improvements and optimization research.

Best regards
Pierre-Yves

Une messagerie gratuite, garantie à vie et des services en plus, ça vous tente ?
Je crée ma boîte mail www.laposte.net
_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise
Kohei Yoshida-5 Kohei Yoshida-5
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

On 07/25/2013 05:57 AM, [hidden email] wrote:

> Hello All
>
>> Message du 23/07/13 19:46
>> And the original goal of my post was to figure out whether there are
>> people who use it, and if yes, what proportion of users (roughly), and
>> whether or not their use cases could be possibly fulfilled by either
>> named ranges or database ranges. Somehow I'm not getting any feedback
>> on that front.
>
> I do user support and training for (thirty) years.
> I've never met one user of this feature.
Ok.  Thanks a lot for providing a data point.  This helps.

>
> However, and this goes against the opinions already given,
> I found that the "automatic find label" option *is* used.
which really bothers me. We should at the very least turn this off by
default, and work toward deprecating this in the future (as Eike also
pointed out).

Any objection to that?  Anyone?

>
> Two concrete use cases (where the find label option has an advantage
> over "named ranges"):
>
> 1. Managing the expansion of the range.
>
> The formula =SUM('Sales'), where 'Sales' is the header column,
> will update if you add amounts. To achieve the same result with
> a "named range" you must either use a "dynamic" name (calculated)
> or plan ahead more than is actually filled range.
Not currently, but the named range can be expanded to add that capability.

Having said that, the expansion of a range sounds more fit for a
database range than a named range.  I'm now more leaning toward database
range as a possible replacement for the labeled range feature.

>
> 2. Adaptation of References
>
> A table with two columns: "purchases" (column A) and "sales" (column B)
> C1 =SUM('Purchase')
> Copy C1 to D1 provides automatically =SUM('Sales') in D1
Good point.  If we don't do this currently with named or database
ranges, we can add this capability to cover this use case.

>
> This can not be obtained directly if "Purchase" is a named range
> (and if the "find label option" is deleted/disabled).
Probably not. But if/when we implement enhanced database range, this use
case will likely be covered.

>
> Do not get me wrong: I do not defend at all costs to maintain
> this feature. I only see it is used and benefits.
This helps, as I was also looking for the missing pieces that the
labeled range provides, so that we can cover those missing pieces in the
other existing range features by extending them.  Looks like it's
plausible to extend the database range to cover these missing pieces.

Thanks a lot for your feedback.

Kohei

--
Kohei Yoshida, LibreOffice Calc hacker, SUSE.

_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise
Eike Rathke-2 Eike Rathke-2
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

In reply to this post by Kohei Yoshida-5
Hi Kohei,

On Tuesday, 2013-07-23 13:47:08 -0400, Kohei Yoshida wrote:

> >Of course it doesn't matter where we actually
> >stick the "defined label" in, having them as part of the database range
> >probably is best because we usually can derive it from the top-row of
> >the database range (don't know currently if Excel allows more than one
> >row for those Table labels, they did a very awkward thing with their
> >labels back then).
> Noted.  Perhaps it was that awkward-ness that prompted them to drop
> this feature in 2007?  I'm just guessing.

Might be one of the reasons (that awkward feature btw was called Stacked
Column Labels, we never implemented that in its implications though).
I guess another reason is the introduction of the XML based file format,
they probably encountered similar problems we do. The automatic lookup
depends on the actual current content of the document, one can quite
easily generate a document using automatic label lookup that when saved
and reloaded again the new lookup actually matches a different cell due
to a nearer cell having the matching content. That was no problem in
binary formats where formulas didn't have to be recompiled. Of course
issues with editing such a formula were also present back then. Sad that
MS introduced it and we had to follow :-/  Having had to implement the
space operator for intersection in this specific case didn't make things
better and already drove me nuts by itself ;-)  Really, space
operator, wtf?!? (yes Excel intersections always work that way)

Remember how the whole story was advertised? Natural Language Formulas ...

<rant off>

  Eike

--
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key ID: 0x65632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
For key transition see http://erack.de/key-transition-2013-01-10.txt.asc
Support the FSFE, care about Free Software! https://fsfe.org/support/?erack

_______________________________________________
Libreoffice-ux-advise mailing list
[hidden email]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise

attachment0 (853 bytes) Download Attachment
pierre-yves samyn pierre-yves samyn
Reply | Threaded
Open this post in threaded view
|

Re: [Libreoffice-ux-advise] Merging Calc's label range functionality with named range.

In reply to this post by Kohei Yoshida-5
Hello

> Message du 26/07/13 16:44
> De : "Kohei Yoshida"
> > I found that the "automatic find label" option *is* used.
> which really bothers me. We should at the very least turn this off by
> default, and work toward deprecating this in the future (as Eike also
> pointed out).
>
> Any objection to that? Anyone?

Ok, no objection

> the expansion of a range sounds more fit for a
> database range than a named range. I'm now more leaning toward database
> range as a possible replacement for the labeled range feature.

This would not be only a "derivative" benefit, it would be a big improvement
in itself for database ranges, highly anticipated.

Best regards
Pierre-Yves



_______________________________________________
Libreoffice-ux-advise mailing list
Libreoffice-ux-advise@...
http://lists.freedesktop.org/mailman/listinfo/libreoffice-ux-advise