Data Studio | How to write case for >= or <= to filter data for Date dimension

user_7866900 0
Viewed 56

Requirement: My data table has 2 fields i.e. Name and Date of joining (DOJ). I want to count users who joined on or before 30-Jan-21.

Solution tried: I created a calculated field using CASE i.e.

CASE WHEN CAST(FORMAT_DATETIME("%Y%m%d", DOJ) AS NUMBER ) <= 20210130 THEN DOJ END.

Issue: After creating the field, I aggregated it by count and used the field in metric but its not giving count of users who joined on or before 30-Jan-21.

Data Table preview

Name DOJ
John Smith 04/01/2021
Dexter Morgan 13/01/2021
Debra Morgan 18/01/2021
Kyle Butler 21/01/2021
Rita Benett 25/01/2021
Angel Batista 31/01/2021
Maria LaGuerta 01/02/2021
Vince Masuka 17/02/2021
Joey Quinn 26/03/2021
Arthur Mitchell 05/04/2021
Thomas Matthews 25/05/2021
google-data-studio
1 Answers

Solution I created a field to convert Date into Number and created another calculated field where I used both the conditions i.e. >= and <= and got the desired result.

Formula for converting Date to text: CAST(FORMAT_DATETIME("%Y%m%d", DOJ) AS NUMBER)

Formula for counting DOJ's in Month of Jan'21 i.e. on and before 30-Jan-21 CASE WHEN DOJ <= 20210130 AND DOJ >=20210101 then DOJ END

user_78669000
Related