1

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

Improve this question

1 Answer 1

Reset to default
1

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.