Difference between revisions of "Talk:Calc/Drafts/Issue 33851"

From Apache OpenOffice Wiki
Jump to: navigation, search
(fill series)
 
(24 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 +
== Fill series ==
 +
 
It looks fairly sensible to me so far.
 
It looks fairly sensible to me so far.
  
Line 4: Line 6:
 
--[[User:Peopleandplanet.org|Peopleandplanet.org]] 18:39, 27 November 2006 (CET)
 
--[[User:Peopleandplanet.org|Peopleandplanet.org]] 18:39, 27 November 2006 (CET)
  
== fill series ==
+
 
 +
: Yet I don't know if it really makes sense to fill a series into a filtered view at all. What would it be good for? Any use case? --[[User:ErAck|erAck]] 15:06, 29 November 2006 (CET)
 +
 
 +
 
  
 
For first, I appreciate the idea to discuss about this issue because is important that OOo will become as users "would like".
 
For first, I appreciate the idea to discuss about this issue because is important that OOo will become as users "would like".
Line 14: Line 19:
 
Actually OOo fill all cells even if they are not visible, and this can be a data loss (at least for me before to known this issue). Now use filter only when there isn't another way.
 
Actually OOo fill all cells even if they are not visible, and this can be a data loss (at least for me before to known this issue). Now use filter only when there isn't another way.
  
Bye
+
Bye<br>
Ambrogio
+
Ambrogio<br>
 
--[[User:Delorea|Delorea]] 22:00, 27 November 2006 (CET)
 
--[[User:Delorea|Delorea]] 22:00, 27 November 2006 (CET)
 +
 +
 +
: I don't think we should have yet another radio button in the dialog for that. If a series should be filled into the entire area one can always do that in an unfiltered view. --[[User:ErAck|erAck]] 15:06, 29 November 2006 (CET)
 +
 +
: Agree with ErAck. Hidden cells should ''''never'''' be overwritten. I think there could be merit in asking the user how s/he wants the series to be calculated for the visible cells filling, between the options 1, 2. As I said above, I'd be happy with it just doing (2), but I concede that it's a rare thing to do anyway, and so presenting the user with the option might be less confusing and more useful. [[User:Peopleandplanet.org|Peopleandplanet.org]] 15:16, 29 November 2006 (CET)
 +
 +
== "filtered/non-filtered range" ? ==
 +
 +
Is it just the other way to describe "hidden/visible cells"?
 +
 +
Yes. If it's too confusing we can change wording. --[[User:ErAck|erAck]] 13:35, 3 January 2007 (CET)
 +
 +
Please, do change the wording. [user:kpalagin, 9:11 (CET) 5 January 2007]
 +
 +
== COPY-then-PASTE ==
 +
 +
This discussion deals with the following scenario:
 +
* somebody applies a filter to a spreadsheet
 +
* then he copies the results
 +
* and then he uses '''paste''' to paste the copied results into a new spreadsheet
 +
* => this was actually '''the purpose''' of the copy operation
 +
 +
There is a formidable problem to this scenario. Lets consider the following spreadsheet:
 +
Row:= Value    Actual Value
 +
A1: = 0          0
 +
A2: = 1          1
 +
A3: = A1 + A2    1
 +
A4: = A2 + A3    2
 +
A5: = A3 + A4    3
 +
... = ...
 +
A9: = A7 + A8    21
 +
 +
* now consider we filter some values out (e.g. we take only odd numbers)
 +
* copy those cells (A2, A3, A5, A6, A8 and A9)
 +
* and decide to paste them in a new spreadsheet
 +
* IF we do NOT paste them as value, then the formula MUST be adapted:
 +
(A2) = 1
 +
(A3) = $sheet_$A$1 + (A2) # (A2) has been copied, so it is OK to use this new (A2)
 +
(A5) = (A3) + $sheet_$A$4
 +
  ...
 +
(A9) = $sheet_$A$7 + (A8)
 +
 +
This was just a simple formula. Consider that there are far more complex formulas, so Calc MUST be able to adjust all these formulas. I believe, this is a daunting task. A very nice workaround is presented next, see paragraphs on '''Paste Reference''' and '''Paste Hardlink'''.
 +
 +
=== Paste Options ===
 +
 +
==== Paste as Value ====
 +
 +
* exactly what it says: evaluate potential formulas and paste only the value
 +
 +
==== Paste as Reference ====
 +
 +
* paste a reference to the original formula
 +
** e.g. (in new cell): '=$sheet$original cell'
 +
** this way, NO formula needs to be updated
 +
 +
==== Paste as Original Content ====
 +
 +
* needs some '''very intelligent''' processing of formulas
 +
* this will be in general very difficult, see the scenario at the beginning of this section
 +
 +
==== Paste as Hard Link ====
 +
 +
* see issue 66817 for Hard Links (http://www.openoffice.org/issues/show_bug.cgi?id=66817)
 +
** Hard Links are NOT yet implemented in Calc
 +
** are more powerful then simple references (see discussion for that issue)
 +
** they would permit propagation of any changes back to the original cell
 +
 +
-- [[User:Discoleo|Discoleo]] 01:19, 12 January 2007 (CET)
 +
 +
 +
: All these ''PASTE'' options are unrelated to the original issue and only add more problems. I actually tend to remove that section completely to avoid confusion. Could we please focus on solving the original issue? Thanks. It also doesn't make sense to introduce an artificial '''= Talk =''' heading and promote one's own sections to H1 level, the page's title already serves well as it's header and always has the H1 attributes, sections should start with level 2. And it is also good habit to '''sign''' one's contributions to talk pages. --[[User:ErAck|erAck]] 17:36, 9 January 2007 (CET)
 +
 +
 +
:: I meant COPY-then-PASTE, so they DO relate to the original problem. I have tried to give now a better explanation (see first paragraph for ''COPY-then-PASTE''). Unfortunately, the current handling of formulas is very problematic and I do NOT see any easy solution, so the ideas presented above should be seriously taken into consideration, as they allow for a very nice workaround. -- [[User:Discoleo|Discoleo]] 01:19, 12 January 2007 (CET)
 +
 +
 +
----
 +
 +
== Hidden vs Filtered-out==
 +
We seem to be concentrating on filtering scenario, but this is not the only way to hide rows/columns. Does our discussion apply to all hiding scenarios? --[[User:Kpalagin|Kpalagin]] 16:28, 15 January 2007 (CET)
 +
 +
:I believe we must find a common handling for filtered and by any other way hidden contents. Currently I can't see any reason to handle those 2 aspects in a different way, but may be someone will find a scenario in what that might be useful? Currently it seems  we should differ between ''visible'' and ''invisible'', however that  ''invisibility'' has appeared. -- [[User:Rainer Bielefeld|Rainer Bielefeld]] - [[User_talk:Rainer_Bielefeld|<font size="+0">✉</font>]] 12:06, 16 January 2007 (CET)
 +
 +
 +
:'''greater complexity'''
 +
:I believe, there should be a common handling for all hidden content, however, I might diverge slighlty from the previous posts:
 +
:* display '''warning''' when operation is to be performed on hidden content (NO disagreement)
 +
:* allow user to choose:
 +
:** perform selected operation:
 +
:*** only on '''visible content'''
 +
:*** on '''hidden content''', too (this diverges from the views of other users)
 +
:* '''REASON:'''
 +
:** sometimes I hide some columns in order to have 2 '''distant columns''' visible simultaneusly, so I can select a record based on data in both these distant columns
 +
:** when I copy such rows, I want to have (almost always) '''''ALL'' columns''' copied (including the non-visible ones, as formulas might be dependent on them)
 +
:** this is probably less so the case when filtering out '''rows''' (BUT I hide out mostly colums)
 +
: - [[User:Discoleo|Discoleo]] 20:05, 16 January 2007 (CET)
 +
 +
=== Filtered vs hidden should be handled differently ===
 +
 +
After a more thorough examination of the problem, I had to change my mind and came to the conclusion that filtering and hiding are used '''very differently'''. This is based both on my own work '''AND''' on the work in a large department where dozens of people work professionally with spreadsheets.
 +
 +
'''Scenario: Hidden Data'''
 +
* I worked much in clinical research. My spreadsheets contained often various patient data used to calculate various clinical scores. One such score (APACHE II) uses some 35 variables of chronic diseases and physiologic variables. The spreadsheets were designed to calculate the final score (one value from these 35 variables) in dozens of hidden columns. So, there were always dozens of '''hidden columns''' necessary to get this score. When I copied some patient data (one or more rows), I wanted to copy always the hidden columns or else the formulas would be broken.
 +
* In a big department (county public health department), most employees worked with spreadsheets that calculated some budgets and automatically allocated the money to various subprograms. Again, most calculations were done inside hidden columns (rarely rows, too). When copying data, one would always want to copy the hidden columns (rarely rows, too).
 +
 +
'''Scenario: Filtered Data'''
 +
* However, when data is usually filtered, this is to select only some specific records. Usually, rows are filtered out entirely, and this rarely breaks formulas (IF copied).
 +
* When I filtered data to select only some patients (or only specific days of hospital stay), I did want to copy the whole rows (with hidden columns => formulas), '''BUT NOT''' the hidden rows.
 +
* again, in the health department, when rows were filtered out because someone wanted to select only the data for specific health care providers, one would want to copy the whole row (including hidden columns), '''BUT NOT''' the hidden rows (belonging to other health care providers NOT intended to be copied)
 +
 +
'''Results:'''
 +
* From this analysis I came to the conclusion that:
 +
** '''hidden data''' is different from '''filtered data'''
 +
** hidden data should be most often copied, too
 +
** data that is filtered out should probably more often NOT be copied
 +
* therefore I would recommend to:
 +
** manage these two methods '''independently'''
 +
** offer user options (both):
 +
*** cut/copy '''hidden data''', and
 +
*** cut/copy '''filtered data'''
 +
** both should be offered '''simultaneously''', so someone may choose 'copy hidden data' AND 'do NOT copy filtered data', yet someone else may choose NOT to copy any of the two hidden varieties
 +
* '''Pasting:''' should in general NOT change hidden content (though I am not so sure: IF columns are hidden as in my APACHE spreadsheets, and I copy one record/row, over a different record, the hidden columns should get copied to the hidden columns, too (hidden over hidden is indeed meant here, not a spelling error))
 +
:-- [[User:Discoleo|Discoleo]] 22:20, 7 February 2007 (CET)
 +
 +
== Types of "Invisibility" ==
 +
 +
I think the various ways in which a cell can become hidden should be noted.
 +
 +
I'm aware of
 +
#Filter
 +
#Hide
 +
#Outline
 +
 +
All result in hidden cells which can still be affected by Cut, Delete, Paste, Format, Move, Fill, and Find & Replace, etc
 +
 +
[[User:Hgreenhough|Hgreenhough]] 12:19, 8 February 2007 (CET)
 +
 +
== Current Behaviour ==
 +
 +
I just got bitten by this again and spent a few minutes figuring out what the current treatment of filtered rows is. I wanted to note this here because even though somewhat improved over previous behaviour, it is now ''extremely'' unintuitive. I did not thoroughly test rows made invisible by Hide or Outline, but it appears they are affected by ''all'' commands.
 +
 +
Do ''not'' affect filtered out rows:
 +
 +
#Copy
 +
#Delete contents
 +
#Delete row
 +
#Format
 +
#Find & Replace in current selection
 +
 +
''Do'' affect filtered out rows:
 +
 +
#Fill
 +
#Cut
 +
#Paste
 +
#Move
 +
 +
[[User:Hgreenhough|Hgreenhough]] 13:05, 8 October 2007 (CEST)

Latest revision as of 11:05, 8 October 2007

Fill series

It looks fairly sensible to me so far.

On the fill series part (the last bit on the page at the time of writing.), I think option 2 is most logical. I think disabling series fill is a cop out! If you go with option 2 then you can still achieve the effect of option 1 through other means (fill the visible cells with a particular value, eg. "fred", then sort the sheet so all your freds are together in a contiguous range, then fill unfiltered, as normal) whereas if you went for option 1, you cannot really achieve the effect of option 2. --Peopleandplanet.org 18:39, 27 November 2006 (CET)


Yet I don't know if it really makes sense to fill a series into a filtered view at all. What would it be good for? Any use case? --erAck 15:06, 29 November 2006 (CET)


For first, I appreciate the idea to discuss about this issue because is important that OOo will become as users "would like".

About the "fill series" I think that is better to permit the 2 option (ask to the user what he want).

So I think that in the fill series option one can activate the option to fill only visible cells, or to fill all cells. By default I think the best is to fill only visible cells (at least one do not loss data without notice). Actually OOo fill all cells even if they are not visible, and this can be a data loss (at least for me before to known this issue). Now use filter only when there isn't another way.

Bye
Ambrogio
--Delorea 22:00, 27 November 2006 (CET)


I don't think we should have yet another radio button in the dialog for that. If a series should be filled into the entire area one can always do that in an unfiltered view. --erAck 15:06, 29 November 2006 (CET)
Agree with ErAck. Hidden cells should 'never' be overwritten. I think there could be merit in asking the user how s/he wants the series to be calculated for the visible cells filling, between the options 1, 2. As I said above, I'd be happy with it just doing (2), but I concede that it's a rare thing to do anyway, and so presenting the user with the option might be less confusing and more useful. Peopleandplanet.org 15:16, 29 November 2006 (CET)

"filtered/non-filtered range" ?

Is it just the other way to describe "hidden/visible cells"?

Yes. If it's too confusing we can change wording. --erAck 13:35, 3 January 2007 (CET)

Please, do change the wording. [user:kpalagin, 9:11 (CET) 5 January 2007]

COPY-then-PASTE

This discussion deals with the following scenario:

  • somebody applies a filter to a spreadsheet
  • then he copies the results
  • and then he uses paste to paste the copied results into a new spreadsheet
  • => this was actually the purpose of the copy operation

There is a formidable problem to this scenario. Lets consider the following spreadsheet:

Row:= Value    Actual Value
A1: = 0           0
A2: = 1           1
A3: = A1 + A2     1
A4: = A2 + A3     2
A5: = A3 + A4     3
... = ...
A9: = A7 + A8     21
  • now consider we filter some values out (e.g. we take only odd numbers)
  • copy those cells (A2, A3, A5, A6, A8 and A9)
  • and decide to paste them in a new spreadsheet
  • IF we do NOT paste them as value, then the formula MUST be adapted:
(A2) = 1
(A3) = $sheet_$A$1 + (A2) # (A2) has been copied, so it is OK to use this new (A2)
(A5) = (A3) + $sheet_$A$4
 ...
(A9) = $sheet_$A$7 + (A8)

This was just a simple formula. Consider that there are far more complex formulas, so Calc MUST be able to adjust all these formulas. I believe, this is a daunting task. A very nice workaround is presented next, see paragraphs on Paste Reference and Paste Hardlink.

Paste Options

Paste as Value

  • exactly what it says: evaluate potential formulas and paste only the value

Paste as Reference

  • paste a reference to the original formula
    • e.g. (in new cell): '=$sheet$original cell'
    • this way, NO formula needs to be updated

Paste as Original Content

  • needs some very intelligent processing of formulas
  • this will be in general very difficult, see the scenario at the beginning of this section

Paste as Hard Link

  • see issue 66817 for Hard Links (http://www.openoffice.org/issues/show_bug.cgi?id=66817)
    • Hard Links are NOT yet implemented in Calc
    • are more powerful then simple references (see discussion for that issue)
    • they would permit propagation of any changes back to the original cell

-- Discoleo 01:19, 12 January 2007 (CET)


All these PASTE options are unrelated to the original issue and only add more problems. I actually tend to remove that section completely to avoid confusion. Could we please focus on solving the original issue? Thanks. It also doesn't make sense to introduce an artificial = Talk = heading and promote one's own sections to H1 level, the page's title already serves well as it's header and always has the H1 attributes, sections should start with level 2. And it is also good habit to sign one's contributions to talk pages. --erAck 17:36, 9 January 2007 (CET)


I meant COPY-then-PASTE, so they DO relate to the original problem. I have tried to give now a better explanation (see first paragraph for COPY-then-PASTE). Unfortunately, the current handling of formulas is very problematic and I do NOT see any easy solution, so the ideas presented above should be seriously taken into consideration, as they allow for a very nice workaround. -- Discoleo 01:19, 12 January 2007 (CET)



Hidden vs Filtered-out

We seem to be concentrating on filtering scenario, but this is not the only way to hide rows/columns. Does our discussion apply to all hiding scenarios? --Kpalagin 16:28, 15 January 2007 (CET)

I believe we must find a common handling for filtered and by any other way hidden contents. Currently I can't see any reason to handle those 2 aspects in a different way, but may be someone will find a scenario in what that might be useful? Currently it seems we should differ between visible and invisible, however that invisibility has appeared. -- Rainer Bielefeld - 12:06, 16 January 2007 (CET)


greater complexity
I believe, there should be a common handling for all hidden content, however, I might diverge slighlty from the previous posts:
  • display warning when operation is to be performed on hidden content (NO disagreement)
  • allow user to choose:
    • perform selected operation:
      • only on visible content
      • on hidden content, too (this diverges from the views of other users)
  • REASON:
    • sometimes I hide some columns in order to have 2 distant columns visible simultaneusly, so I can select a record based on data in both these distant columns
    • when I copy such rows, I want to have (almost always) ALL columns copied (including the non-visible ones, as formulas might be dependent on them)
    • this is probably less so the case when filtering out rows (BUT I hide out mostly colums)
- Discoleo 20:05, 16 January 2007 (CET)

Filtered vs hidden should be handled differently

After a more thorough examination of the problem, I had to change my mind and came to the conclusion that filtering and hiding are used very differently. This is based both on my own work AND on the work in a large department where dozens of people work professionally with spreadsheets.

Scenario: Hidden Data

  • I worked much in clinical research. My spreadsheets contained often various patient data used to calculate various clinical scores. One such score (APACHE II) uses some 35 variables of chronic diseases and physiologic variables. The spreadsheets were designed to calculate the final score (one value from these 35 variables) in dozens of hidden columns. So, there were always dozens of hidden columns necessary to get this score. When I copied some patient data (one or more rows), I wanted to copy always the hidden columns or else the formulas would be broken.
  • In a big department (county public health department), most employees worked with spreadsheets that calculated some budgets and automatically allocated the money to various subprograms. Again, most calculations were done inside hidden columns (rarely rows, too). When copying data, one would always want to copy the hidden columns (rarely rows, too).

Scenario: Filtered Data

  • However, when data is usually filtered, this is to select only some specific records. Usually, rows are filtered out entirely, and this rarely breaks formulas (IF copied).
  • When I filtered data to select only some patients (or only specific days of hospital stay), I did want to copy the whole rows (with hidden columns => formulas), BUT NOT the hidden rows.
  • again, in the health department, when rows were filtered out because someone wanted to select only the data for specific health care providers, one would want to copy the whole row (including hidden columns), BUT NOT the hidden rows (belonging to other health care providers NOT intended to be copied)

Results:

  • From this analysis I came to the conclusion that:
    • hidden data is different from filtered data
    • hidden data should be most often copied, too
    • data that is filtered out should probably more often NOT be copied
  • therefore I would recommend to:
    • manage these two methods independently
    • offer user options (both):
      • cut/copy hidden data, and
      • cut/copy filtered data
    • both should be offered simultaneously, so someone may choose 'copy hidden data' AND 'do NOT copy filtered data', yet someone else may choose NOT to copy any of the two hidden varieties
  • Pasting: should in general NOT change hidden content (though I am not so sure: IF columns are hidden as in my APACHE spreadsheets, and I copy one record/row, over a different record, the hidden columns should get copied to the hidden columns, too (hidden over hidden is indeed meant here, not a spelling error))
-- Discoleo 22:20, 7 February 2007 (CET)

Types of "Invisibility"

I think the various ways in which a cell can become hidden should be noted.

I'm aware of

  1. Filter
  2. Hide
  3. Outline

All result in hidden cells which can still be affected by Cut, Delete, Paste, Format, Move, Fill, and Find & Replace, etc

Hgreenhough 12:19, 8 February 2007 (CET)

Current Behaviour

I just got bitten by this again and spent a few minutes figuring out what the current treatment of filtered rows is. I wanted to note this here because even though somewhat improved over previous behaviour, it is now extremely unintuitive. I did not thoroughly test rows made invisible by Hide or Outline, but it appears they are affected by all commands.

Do not affect filtered out rows:

  1. Copy
  2. Delete contents
  3. Delete row
  4. Format
  5. Find & Replace in current selection

Do affect filtered out rows:

  1. Fill
  2. Cut
  3. Paste
  4. Move

Hgreenhough 13:05, 8 October 2007 (CEST)

Personal tools