Hi all –
I am trying to put together a way to view location attendance data dynamically over time.
There are several use cases that I have for this, and I’m unsure whether I’m trying to shoehorn them all into one graph/chart/table or whether it would be better to break it out into multiple.
Ideally, I’d be able to visualise answers to the following example questions;
- How many times did I visit the office last week/month?
- How many times did I visit the office last month, compared to the same month last year?
- How many times did I collect my son from nursery last week/month? How many times did my wife collect him?
Important to note here that a visit to a zone should only be logged once per day. For example while I’m at the office I often leave for lunch during the day and return in the afternoon; I wouldn’t want this logged as two visits.
The combinations of time periods/aggregations (i.e. visit count per month for the last 12 months, visit count per day for the past week/month) should be variable so that the data can be viewed dynamically.
Being able to use controls such as those at the top of the Energy dash would be excellent, so as to be able to easily navigate through those timer periods/aggregations;
I use MySQL as a backend for my Recorder and I have attempted to populate visit data into a bespoke new table, but I’m left stumped at how to view it, or if indeed I’m populating it in the most efficient format to be picked up and viewed.
I have installed InfluxDB and Grafana and attempted to do the same there, but again I’m not sure my data is in the correct format.
In addition, I currently have counter sensors dynamically created and updated via MQTT for every person/zone combination that use the “total_increasing” state class.
My next step will be to begin playing with History Stats sensors that look at those counters, but I can’t see any way to have these be dynamic. The only way I can think of using these would be to create a whole bunch of them per counter using arbitrary, pre-conceived time periods, i.e. today, yesterday, this week, this month, etc. Unless I were to create month-before-last, month-before-that, etc. I wouldn’t be able to view the breakdown over time.
Has anyone achieved, or even seen, any thing that comes close to what I’m looking at doing here? Any help would be greatly appreciated.