Google Data Studio Case When Two Sets of Criteria

Question

I have a dataset in Google Sheets that records updates to projects over time:

Update_Date   Project_Code   Status
01/09/21      0001           Proposal
01/09/21      0002           Delivery
01/09/21      0003           Business Case
01/10/21      0001           Business Case
01/10/21      0002           Delivery
01/10/21      0003           Delivery

I am using this data as a Data Source in Google Data Studio. Is it possible to produce a count of the number of projects that have moved between Status values over time? For example, for the update on 01/10/21, there is one project that has moved from Proposal to Business Case (0001).

I have tried to do this by creating a field and using COUNT_DISTINCT(CASE WHEN Update_Date = 01/09/21 and Status="Proposal" and Update_Date=01/10/21 and Status="Business Case" THEN Project_Code ELSE NULL END) but I get an incorrect value of 0, which I suspect is because I am referencing the same two variables twice in the one formula.

score 0 · Accepted Answer · answered Sep 21 '21 at 13:42

This was solved by converting each combination of values to a number in a separate field x:

CASE 
WHEN Update_Date=01/09/21 and Status="Proposal" THEN 1
WHEN Update_Date=01/10/21 and Status="Business Case" THEN 2
ELSE 0
END

then blending this data (using the automatic SUM aggregation) with a distinct count of the Project_Code field and finally filtering the result for those Project_Code values where x is equal to 3

Google Data Studio Case When Two Sets of Criteria

1 Answers1