Data Studio
1.8K members online now
1.8K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Custom Dimension

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello, 

 

I have a table in Big Query and for simplicity lets say it has 2 columns 

id , result

The value in result could be success, fail , cancel. 

 

Now I want to have a table chart in Google Data Studio like following

 

Category        Count

------------------------------

Attempt          10

Success           5

Fail                   2

Cancel             3

 

Only issue is how to add  Attempt to the table. 

 

Basically what I am looking for is a condition something like 

If result =success | fail | cancel then "Attempt"

If result = success then "Success"

If result = fail then "Fail"

If result = cancel then "Cancel"

 

Pls. let me know if it is possible to make such Dimension.

 

Thanks

 

 

 

Custom Dimension

Follower ✭ ✭ ☆
# 2
Follower ✭ ✭ ☆

Hello Naveem,

I think you cantry to create a new dimension based on a CASE WHEN + REGEXP formula to achieve your goal.

 

To do that, you should edit your data source and click on the "+" icon "create new field". You will call it "Category" and then the formula could look like this (Sorry I'm not 100% sure of the Regex writing).

 

CASE WHEN REGEXP_MATCH(result, "(success | fail | cancel)") THEN "attempt" WHEN REGEXP_MATCH(Result, "(sucess*)"  ) THEN "Success" WHEN REGEXP_MATCH(result, "(Fail*)"  ) THEN "Fail" WHEN REGEXP_MATCH(result, "(Cancel*)"  ) THEN "Cancel" ELSE "other" END

 

Hope it will help!

Have a nice day,


Anthony

Custom Dimension

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks Anthony for the reply.

I tried the formula below

 

CASE
WHEN REGEXP_MATCH(result, "(success|failed|canceled)") THEN "Attempt"
WHEN REGEXP_MATCH(result, "(success*)" ) THEN "Success"
WHEN REGEXP_MATCH(result, "(failed*)" ) THEN "Failed"
WHEN REGEXP_MATCH(result, "(canceled*)" ) THEN "Canceled"
ELSE "other"
END

 

With this formula for all records the first When always matches the criteria and only Attempt is shown.

Success, Failed and Canceled are never shown. 

 

Did I miss something?

 

Custom Dimension

Follower ✭ ✭ ☆
# 4
Follower ✭ ✭ ☆

Hello Naveen,


Sorry for my lack of response yesterday.


I'm not 100% sure of the reason why the formula fails (probably related to the fact that the 4 conditions produce overlapping results).

 

I don't know if it will meet your needs, but as a workaround, you could try to build the 4 metrics individually with a unique CASE condition for each. And then, add the 4 metrics to your table.

 

Have a great day!
Anthony