Search code examples
google-bigquerypivotlooker-studio

Google Looker Studio simplest way to make line chart from aggregated monthly data

Having a little trouble figuring this out.

Data:

Student ID Sep Attendance Oct Attendance
243156544 100 91
237831714 91 100
258358928 100 86

How would I create a line chart in Looker Studio like below?

enter image description here

So far I have tried with averaged fields, preprocessing data in BigQuery, and creating from a table within the Studio report (which I now don't think is possible). In Excel I would create a pivot chart and then a line pivot chart.

Any ideas? I am new to this. Thank you.

Solution

  • You need to change your data model. But first of all, in order to make sure that the order of the months are correct in the resulting diagram, rename your columns in your original table. Rename Sep Attendance to September, Oct Attendance to October and so on.

    Now "unpivot" your table in BigQuery like this:

    SELECT * FROM <Your Table>
UNPIVOT(Attendance FOR Month IN (September, October))

    Save the results as a new table. The new table will look like this:

    Student ID Month Attendance
    243156544 September 100
    243156544 October 91
    237831714 September 91
    237831714 October 100
    258358928 September 100
    258358928 October 86

    Now use this as your data source in looker studio. In the data source settings, make sure to set the type of Month Field to "Date and Time > Month". Now add a line chart with this data source, set dimension to Month and Metric to Attendance. Click on the "SUM" icon next to the metric and set the aggregation method to Average. Now it will work as shown in your diagram.