We’d like to create a report showing our current automation levels against the past 7 day average and the past 30 day average. I was hoping to display this as a set of 3 bars - however, we can’t figure out (even with support’s help) how to get all 3 averages in the same metric set/visualization since we’re using just one data connector to produce each of the 3 numbers while varying just the time frame. Creating 3 different data connectors isn’t going to work - the connector is enormous and we don’t want to store 3 versions of it. Anyone out there smarter than us who has solved this problem?
Thanks!
Compare against benchmarks
1 Like
Where is the data stored?
SQL?
Yes, it’s in a Postgres database.
They approach would try is to write a query that gives me all the data I need. This is not going to be a simple query you might have some Sum Over Partitions and Pivoting (sum) (that what it is in SQL).
You only need one Data Connector to your DB, but you might make a few Data Cubes (one connect supports all your data cubes that are reading from your Postgres DB).
There a few things you can do if there is a lot of data that would be returned in a data cube:
Ask how far back in time do they need to go? I have several that they only want to compare to last year, so the query itself has a where clause that only goes back to January 1st of the previous year.
You can also warehouse the data by changing the storage type of the Data Cube.
I in most cases do both.
Yes, thanks!
Turns out we can’t do what we need in a table - but we changed to a bar chart Dundas can give us the comparison we need.
Thanks for taking the time to help 
Have you investigated using formula visualisations?
You make 3 tables off canvas, one for each of your timeframes and then create a formula visualisation that includes all 3. You can then filter each of the 3 differently but still use all the measures in one table.
I’ve done something similar before and it worked fine for me
Hi Cathyann,
If these types of comparisons are done often in your business, this really should be done in the back end, i.e. in Postgres, so that you can reuse the query elsewhere or easily copy and modify it for other, similar purposes. The SQL in question is not that difficult and, once learned, can be fed into any number of visualizations, including tables. Otherwise, you’ll find yourself using different techniques for different visualizations, etc., which can become a bit of a maintenance nightmare.
Period over period comparisons are extremely important to us, so we have a set of stored procedures that all start with a PoP prefix that feed a great variety of visualizations. Anything changes in those requirements, we know exactly where to go and the techniques are the same for all uses.
Wayne