looker-studiosparklines

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

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.

Solution

  • 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.

    enter image description here

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