subtract dates in looker table calculations

Subtract dates in looker table calculations how to#

The result? A line or, let's say, bar chart that helps you assess the distribution of your chosen metric (like events or users) as a percentage within a day or a month. His trick involves a simple blend of two tables and applying a straightforward calculated field. However, I found this nifty solution from Eric Boissonneault, who recently rocked the MeasureSummit. Unfortunately, Looker Studio doesn't offer this functionality out of the box.

Say, we want to track changes in user or events distribution across device categories or countries day by day or month by month. When we're crunching data, there's often a need to figure out percentages based on some dimension. The columns of “first difference” ( the subtraction between the value of the current row and the value of the previous row, so the day before in our data set) are made for the scatter chart (third chart in the demo).(so I thought until I stumbled upon Eric Boissonneault's video) You will find below a screenshot of the data set sample.

In my case, I stored and prepared this data set through a BigQuery query. Let’s see the result before jumping to the configuration of our drop down menu (or an open fixed-size control) to choose the metric we wish to display in our charts.Īny set of metrics is compatible with the metric selector, even for automatic aggregated metrics (blue fields in the data source). The metrics have been gathered on a simple BigQuery table and they are ready for visualisation through Looker Studio. I would like to see the timing correlation of the notifications with my store metrics but there is no technical link between separate data systems (offline for most of them), except the timing of course (date as unique dimension). To communicate about promotions, this store sends phone notifications to their customers & leads. My data set includes store metrics: Let’s a shop owns a Google My Business file, receives calls for quotation, and sells products. So, I built a use case to implement a part of Tim talk. He explained how we can analyze & educate, with accessible calculation technics, to confirm/deny speculations about temporal correlations, too often quickly validated by basic parallel trends: “Time-series analysis: Important concepts, handy techniques & some cautionary tales”. I was inspired by the conference of Tim Wilson at Superweek 2023. In my case, I’ve decided to use this opportunity to see how Looker Studio could address subjects about time-series analysis for correlation findings. Of course, you can follow this tutorial for any list of metrics, available in one of your data source or blended data.

The related article needs to be updated for minor changes (no more parameter value wrapper to use it as part of a calculated field) but the main part is still relevant. The same principle is also possible with a dimension selector.

Subtract dates in looker table calculations how to#

You know how to display data but not what

You need to reduce the number of data requests, and so, what is not loaded when the page is displayed is also what will not affect costs or quotas, measure requests just on demand.

Your chart configuration is quite sophisticated and parameter variables are enough to not repeated elements, easier to maintain in the future.

You don’t want to overload your report with chart sets, replicated for each metric.

Displaying the main metric is your concern but the same charts can be useful for secondary measures (basically, optional metrics, shared across more than one chart).

Taking of avantage of a simple configuration, this kind of control gets a lot of benefits and can be implemented for various reasons: This allows to change the measure displayed inside a set of Looker Studio charts (formerly known as Google Data Studio) as report reader. Let’s go back to the basic to create a simple metric selector.