Bug 151027 - Allow the use of non-absolute references with Sparkline formulas
Summary: Allow the use of non-absolute references with Sparkline formulas
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All Linux (All)
: medium enhancement
Assignee: Not Assigned
: 151028 (view as bug list)
Depends on:
Blocks: Sparklines
  Show dependency treegraph
Reported: 2022-09-17 13:45 UTC by aplatypus
Modified: 2023-02-16 17:11 UTC (History)
0 users

See Also:
Crash report or crash signature:

Sparkline reference screenshot: CALC.Absolute-reference-formula.png (19.20 KB, image/png)
2022-09-17 13:47 UTC, aplatypus

Note You need to log in before you can comment on or make changes to this bug.
Description aplatypus 2022-09-17 13:45:16 UTC
* I like the fact that we now have Sparklines in Calc.
* I have attached a screenshot
   1. CALC.Absolute-reference-formula.png

* In the image #1, you can see the forula has data for the Sparkline from column U (C20) to column X (C24) on sheet 'daily summary'
* This is an absolute cell range fixed to just one sheet.  
*  The absolute reference is the ONLY refernce that a Sparkline cell will accept/work.

* This is highly inconvenient, not to mention labour intensive to work around.  For example in tonight's use case I have daily data for 52 weeks.  The design is to show a moving 4-week trend for day of the week.  (See steps to reproduce)

Steps to Reproduce:
* The current use case specification is to show a moving 4-week trend for day of the week.  

1. The initial idea was to build a Sparkline from 4 x Wednesdays R[-21]C9, R[-14]C9, R[-7]C9, and RC9. 
  x This was Not allowed -- No error message is displayed however.
  x The only kind of data that worked was a contiguous range.  Again not message was shown.  The Sparkline didn't display.  

2. The workaround for this was to put the 4 x Wednesday data points on a row as a continuous row of cells for the current day's row.
  x 'daily summary'!U76:X76
  x again, the sheet name is compulsory
  x cell references are absolute, not relative

3. Naturally, I wanted to repeat the working Sparkline for the other days of the week.  I copied the formula to another row, with the 4 weeks time series on the same row.  

4. The copied Sparkline looked OK, but it should have been a different shape -- And it was not.  The copied cell showed the same formula, and Sparkline graphic as its source ... Because the copy copied the absolute reference from the original Sparkline cell.  

5. Consequently I needed to individually edit the formula for the new cell to refere to its row, not the original row.

6. While that actually worked; it is not what we use spreadsheets for.  If I need to manually enter cells in this way, I'm better off using Python or something to work my data.

7. Another thing the current restrictions prevent or at least make incredibilly difficult are various "What-if" models or equations.  Given the lack of mutability in the Sparkline facility at present

Actual Results:
* All formulas need to be hard-coded to the absolute cell references
* Copy or moving Sparkline cells is a labour intensive, manual procedure.
* Larger "What-if" scenarios are almost infeasible, and smaller "What-if" or sensitivity analysis becomes onerous, labour intensive and hence error prone.  Potentially worthless.

Expected Results:
* Sparkline cells must be copy and move capable.  Formula must be suitable for copying and use in other locations and work in an expected way.  
* Sparkline formula and reference handling should follow the conventions and behaviour for similar kinds of formula.  For example; the way Sum(), Average(), etc. work.
* Standard formula conventions must be adhered to
* Relative references ought to be the usual default
* Worksheet names are only used when the target is on another sheet
* All valid formula forms should be allowed.  I was not able to use a list or cells.  Only an absolute contiguoous range was able to produce a Sparkline.  
  x This is not the usual spreadsheet case.
  x I see no reason why other suitable forms like a list is invalid(??).
* Informative error messages should explain why/what is happening.

Reproducible: Always

User Profile Reset: No

OpenGL enabled: Yes

Additional Info:
Version: / LibreOffice Community
Build ID: 3c58a8f3a960df8bc8fd77b461821e42c061c5f0
CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded
Comment 1 aplatypus 2022-09-17 13:47:52 UTC
Created attachment 182521 [details]
Sparkline reference screenshot: CALC.Absolute-reference-formula.png
Comment 2 Buovjaga 2023-02-16 17:11:53 UTC
*** Bug 151028 has been marked as a duplicate of this bug. ***