* I like the fact that we now have Sparklines in Calc.
* I have attached a screenshot
* 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
* 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.
* 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.
User Profile Reset: No
OpenGL enabled: Yes
Version: 188.8.131.52 / 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
Created attachment 182521 [details]
Sparkline reference screenshot: CALC.Absolute-reference-formula.png
*** Bug 151028 has been marked as a duplicate of this bug. ***