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.
CodePudding user response:
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