Dynamic Graph in Calc?

[LibreGuy]

Hi guys,

I’m wondering if it possible to create a dynamic graph in Calc. Something that looks like this:

I will explain a bit more based on a simple example (not the exact case I need it for, but easier to understand). In the image above you see several categories like “running” and “walking”.

Now here’s the deal. In my Calc spreadsheet file there a 2 different tabs/sheets. In the first tab I want to register what I’m doing during the day. In real life I want to register my business activities, but to keep things simple let’s stick to the example of physical excercise as seen in the image.

So in the first sheet I want to register what I’m doing and for how long. So for example, column A contains the activity, B the start time, C the end time and D the calculated period of time I have been excercising. For example, something like this:

        A        B        C        D
1   activity   start     end     time
2   running    13:10    13:25    00:15
3   walking    13:45    14:00    00:15

The amount of rows will have a maximum of 5.000.

Now what I’m wondering is if it is possible to dynamically generate a horizontal graph (like the image) on the second sheet that shows the percentage (not the amount of time) I have spent on each activity?

So … based on the input of the 2 rows above, the graph should show 2 horizontal bars. Since I have spend 15 minutes on each activity both bars should be of equal length and indicating a percentage of 50%.

Also … when I would add another category in column A, for example tennis, this should automatically show up in the graph as well.

I hope my explanation is understandable (English is not my mother language). If not, please say so.

Thank you for taking the time to read this, and any help would be very welcome!

[Lupp]

Every Chart getting its data from a CellRange (or a few CellRanges) is dynamic.
You have to prepare the data, however, to meet your purposes.
Start with a complete list of your activities, use some kind of SUMIF or SUMPRODUCT, calculate the ratios … and it will be easy to create the Chart.

[LibreGuy]

Thanks Lupp, the dynamic part was especially about recognizing the categories without hardcoding them and then find the total value of such a category. I can find the total time of all categories, but how do I “detect” the categories that are in column A and how do I add together all of the time periods of one category?

[Lupp]

The ‘Data range’ of a chart must be a range address constant or a list of range address constants separated by semicolons.
It might be preferable if calculated ranges (using OFFSET) or named ranges (which well may be calculated) were allowed. However, I did not even find a feature request to that effect. There are similar ones for “Pivot Tables” and for “Print Ranges”.
Thus you may prepare everything allowing for variable data ranges, but at the end you will have to open the ‘Data Ranges…’ dialogue to make the final adaptions.
See attached example: ask62889ChartWithAutomaticallyCompactedLabels001.ods

[LibreGuy]

Thanks Lupp. You put some effort in it I appreciate that very much.

Since this is new to me, it’s quite hard to understand. However, am I right to say that you made 2 set-ups (possible scenario’s) which I could use, and that the one one the left is more hard-coded where the one on the rigth is more dynamic? Is that a valid conclusion?

[Lupp]

The left side is just doing as I suggested in my first comment. The right side (including the yellow helpers) creates a list of activities automatically compacted (in reverse order of the final occurrence). An automated adaption of the data ranges for the chart is (presently) not supported.
The ones in column K indicate the last occurrences. In column M the relative row numbers of these occurrences are found.

[LibreGuy]

Thanks I sort of understand now. I will figure out if and if so which method I will be using. At least I’ve got a starting point now. Thanks for your help! Truly appreciate it.

[newbie-02]

had a similar wish some time ago …
just an idea, not yet tested:
charts are somewhat ‘dynamic’ as they adapt ranges when you insert rows or columns into them,
do that by a macro whenever needed and you’re done?
input data - macro triggered - ‘filter-copies’ activities to a temporary list without dups - compares length of that list with data range for chart - inserts add. row / column if needed - copies activity categories from temporary list into data range, copies formulas needed to extract cumulated times - done?
other algorithms might work too, check if activity already in cumulated list - if not add row and add activity - copy time cumulating formula,