CODEWITHSUNDEEP
looker-studio
Sergei Eensalu
Sergei Eensalu

Reputation: 386

Filtering differents date columns by two or more dates in data studio

I have a list of objects. In my case, the object is a contract (agreement). The contract has two dates: the beginning and the end of the contract. Both dates are different columns.

I want to make it possible to filter all contracts by start and end date. for example, all contracts where the contract start data is 01.03.2020 - 31.03.2020 and end date is 01.11.2020 - 30.11.2020.

If I use the date range control, then it looks only the date range demension, which can be only one field (for example, only the beginning of the contract). But i need a solution where i can filter by one date or/and another date.

please help me how I could solve this problem.

Data structure:

enter image description here

Table settings:

enter image description here

Upvotes: 1

Views: 2240

Answers (1)

Nimantha
Nimantha

Reputation: 6471

One way that it can be achieved is by creating two Calculated Fields and adding them to two Advanced Filter Controls, where the values will be filtered in the YYYYMMDD format e.g. 27 Nov 2020 would be 20201127 (adapt the format as required by changing the inputs in the FORMAT_DATETIME function):

1) Start Date

1.1) Calculated Field

CAST(FORMAT_DATETIME("%Y%m%d", start_date) AS NUMBER )

1.2) Control

  • Type: Advanced Filter
  • Control Field: Start Date
  • Search Type: >=

2) End Date

2.1) Calculated Field

CAST(FORMAT_DATETIME("%Y%m%d", end_date) AS NUMBER )

2.2) Control

  • Type: Advanced Filter
  • Control Field: End Date
  • Search Type: <=

Editable Google Data Studio Report and a GIF to elaborate:

Upvotes: 1

Related Questions