Google Sheets to Google Data Studio - Year Comparison Combo Chart

0
votes

I'm very much a beginner to Google Data Studio. I've been tasked with moving over our monthly reports from Google Sheets to the more attractive Data Studio.

I'm stuck on what I assume is actually a very simple chart but I just cannot seem to be able to recreate it. Would anyone be so kind to advise, please?

  1. This combo chart is in the current Sheet:

    • X axis: Sales

    • Series: 2019, 2020

    • Sales Combo Chart - Year vs Year:
      sales combo chart - year vs year

  2. It uses data from a table with the following set up (example figures):

    • Table - Year, Month, Sales columns:
      Table - Year, Month, Sales columns

  3. The best I can create in Data Studio is a bar chart:

    • Dimension: Month
    • Breakdown Dimension: Year
    • Metric: Sales
    • Sort: Year

    • Secondary Sort: Sales

    • Sales Bar Chart - Year vs Year: sales bar chart - year vs year

1
google-sheetsgoogle-data-studio

1 Answers

0
votes

Created a Google Data Studio Report (replicating the Google Sheet) to demonstrate with a 3 step process below:

Step 1: Date YYYYMMDD

Create a YYYYMMDD Date field at the Data Source-level; this can be achieved by using the CONCAT function to consolidate the Year and Month fields and then adding a Day value (using 15 to represent the 15th day in the formula below) and combining the TODATE function to create a Date format recognised by Google Data Studio:

  • Name: Date YYYYMMDD
  • Formula: TODATE(CONCAT(Year, Month, "15"), "%Y%b%d", "%Y%m%d")
  • Type: Date (YYYYMMDD)
  • Optional: Disable the Month and Year fields

GIF to visualise Step 1:

4]

Step 2: Date Range

Add a Date Range Control and set the range to Advanced:

  • Start Date: Today Minus 1 Year
  • End Date: Today Minus 0 Year(s)

GIF to detail step 2:

6]

Step 3: Chart

Add a Time Series Chart with the following:

  • Date Range Dimension: Date YYYYMMDD
  • Dimension: Date YYYYMMDD
    • Granularity: Month
  • Breakdown Dimension: Date YYYYMMDD
    • Granularity: Year
  • Metric: Sales
  • Breakdown Dimension Sort: Year in Descending order
  • Style:
    • Series no. 1: Bars
    • Series no. 2: Line; Show Points: Select

GIF to explain Step 3:

8]

Step 3 (Alt): Chart (Comparison Date Range)

Added a New Page to the Report to provide an alternative approach that uses a Comparison Date Range in a Time Series Chart:

  • Date Range Control: This Year
  • Dimension: Date YYYYMMDD
    • Granularity: Month
  • Comparison Date Range: Previous Year

GIF to elaborate on expand on Step 3 (Alt):

11]