CODEWITHSUNDEEP
looker-studio
user963070
user963070

Reputation: 639

How can I get the date range control to act on the created date field (calculated field) and not the actual date field?

I have multiple tables in a Google Data Studio report:

Table 1 (Input):

Name Date
Sally Jan 14, 2022
Mike Jan 11, 2021
Bob Jan 12, 2022

Table 2 (Input):

Name One Year From Date
Sally Jan 14, 2023
Mike Jan 11, 2022
Bob Jan 12, 2023

Note: One Year From Date is a calculated field based on the Date field: datetime_add(Date, interval 1 year)

When I add a date range control, it filters based on the date that all the calculated fields are based on (Date field). For example, if the date range control is from Jan 1, 2022 to Jan 31, 2022, the results are:

Table 1 (current output):

Name Date
Sally Jan 14, 2022
Bob Jan 12, 2022

Table 2 (current output):

Name One Year From Date
Sally Jan 14, 2023
Bob Jan 12, 2023

with the desired outcome being:

Table 1 (expected output):

Name Date
Sally Jan 14, 2022
Bob Jan 12, 2022

Table 2 (expected output):

Name One Year From Date
Mike Jan 11, 2022

How do I get the filter to filter based on the actual date rather than the calculated field original date?

Google Data Studio Report

Upvotes: 2

Views: 2395

Answers (1)

Nimantha
Nimantha

Reputation: 6471

It can be achieved by matching the date range dimension field of each table with the respective date field in each table:

Table 1

  • Date Range Dimension: Date
  • Dimension 1: Name
  • Dimension 2: Date

Table 2

  • Date Range Dimension: One Year From Date
  • Dimension 1: Name
  • Dimension 2: One Year From Date

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

4

Upvotes: 0

Related Questions