How to create a Sparkline chart for for a particular name with values depending on the fields of other cells?

Question

I need help creating a sparkline chart for an example data as below:

name	timestamps	test	action
try 1	2022-11-16 22:39:35.653819+00:00	TRUE	TRUE
try 2	2022-11-16 22:39:33.171203+00:00	TRUE	TRUE
try 1	2022-11-16 22:39:30.699472+00:00	FALSE	TRUE
try 4	2022-11-16 22:39:27.711734+00:00	TRUE	FALSE

Let's say I am only trying to create a sparkline chart for try 1 against the timestamps.

What I want the chart to show is:

If test and action is TRUE and TRUE then for that timestamp Try 1 value is 2 If test and action is TRUE and FALSE then for that timestamp Try 1 value is 1 If test and action is FALSE and FALSE then for that timestamp Try 1 value is 0

I don't want to 'code' this into the google sheet as the data uploaded will be refreshed automatically and all my code will be gone when the data is refreshed.

Is there anyway I can code this into the sparkline chart itself on google data studio?

I tried creating a field in the metric section of the sparkline chart but it gives a system error.

Answer 1

hopefully this should get you started.

fix the timestamp to be studio-compatible

PARSE_DATETIME("%F %H:%M:%E*S",LEFT_TEXT(timestamps,26))

get the value score from test and action

CASE WHEN action='TRUE' THEN 1 ELSE 0 END+CASE WHEN test='TRUE' THEN 1 ELSE 0 END

you can use both these calculated fields as dimension and metric for spark chart.

lastly you can apply a filter to chart to see just the 'try 1' score.