Did you view the file? Is there a way to get it into ASKLo or do I have to leave the link to my GDrive in perpetuity?
The usecase for SUBTOTAL is about using Filters, if you want to not use Filters at all, or in humptie combinations with other Criteria, you should not use SUBTOTAL, but something of SUMIF[S], COUNTIF[S], SUMPRODUCT.
Sorry I get the Audio-part only, no video!
is it ok to mail it to you direct?
I use the COUNTIF() in my helper cells because they count unconditionally of the filter setting but the SUBTOTAL() at the top of each helper column then only accrues the result if it’s not “hidden” by a filter. Best of both worlds, I can exclude on generalities and include on specifics.
I just downloaded it and allowed it to play directly from the download and also only got the audio. However, using VLC and just reloading, it seems to work
Makes no difference… try to upload as .mp4 instead .mkv
Audio got a bit clipped
Maybe this is more indicative
I can have any combination of active filters on both data entry and subsequent calculations, ADDITIONALLY I can identify a value <,<=,=,>=,> and the appropriate column and it returns the count of those values in the target column that are visible anywhere in that column - currently 3500 rows x 3 columns + the 430 rows of two columns being the two rightmost elements of the framed row.
You are mis-using a spreadssheet as a database. This is one of the biggest misconceptions in computer history. The pivot table feature is a big concession to all the users who struggle with this (the majority I think). It works like a database query. Once you run a query, it returns an entire table from another table and dumps the result to a range of spreadsheet cells. But you have to run that query. I have seen a pivot table on a Calc sheet aggregating millions of database rows in under a minute time. You must not expect that every change of a value re-runs the database query automatically. That could make the whole thing unusable.
RIght-click>Refresh does the trick. or Data>PivotTable>refresh or
ThisComponent.Sheets(0).DataPilotTables.getByIndex(0).refresh()
You may put the pivot on another sheet and run such most simple macro when the sheet is activated.
I think I probably have a hybrid - perhaps you could provide further insight from the following apprisal;
To start off, the rows are inverted so the oldest is at the bottom. The rows are not isolated events, each row inherits its opening status from the previous row’s close with the initial movement quantifying the change in value from the prior final status. The elements in columns JLM&N either compare with the previous row, compare with the previous period or indicate the cumulative movement or average movement throughout the current 8 event period. The top 5 rows above the “freeze” are more of a dashboard with all the aggregation I need and they are “real-time”. The only part that vaguely resembles a DB Query is the little module C1:E2 which is the function I was investigating, to ascertain whether it could be improved with an array function. Originally, this module covered 5 rows with the data extrapolated for the rows J-N so each column could be “interrogated” whilst the others remained visible. The jury is still out on whether the consolidation of the query to uniquely cover each of the five columns with a single “window” is the final cut - it’s a two minute change to revert.
The filters obviously permit all their intrinsic functions and allow me to select and/or sort the data by an almost infinite variety of values. The nature of LO’s ability to retain cell connections when the data is sorted or filtered makes it very compliant (and forgiving). Can a database do that when the records are really just a 30000 cell stream of continuous movements?
I never have to sort it as the rows are entered in the natural order so it’s really just a case of filtering to exclude all of a particular class or classes of event. Conditional colour formatting defined within the columns provides the perfect visual “flow” pattern when like events are filtered together and indeed for the entire sheet when everything is visible.
Personally, I think it’s probably better than a database could provide and I don’t need to worry about defining reports (pivots) or refreshing them as it never leaves the monitor.
I am keen to receive any observations you may have and also, if it’s deemed a database, the least disruptive manner of transferring it to a new structure.
If I live to a hundred my sheet still won’t have 100000 rows. Is that big enough for a database?
I just tried to explain why an aggregation query does not refresh automatically. The developers who implement this have to keep in mind that the source data table may be big.