CODEWITHSUNDEEP
calculated-fieldlooker-studio
Andi Jankovič
Andi Jankovič

Reputation: 19

Data Studio - Calculate Fields - Substraction of Total Events

I would like to create a calculated field, which would substract metrics total events from the event with more total events to event with less total events. Afterwards, I would like to create a line graph of this in DataStudio.

Basically, I would like to substract total events of the following events:

Event Category: Game

Event Action: Game Session Started

minus

Event Category: Game

Event Action: Game Session Finished

I was trying in CASE with functions such as ABS, SUM etc.. however, I can't seem to find a solution. Thank you.

Here is an example: Example

Upvotes: 0

Views: 3223

Answers (3)

Serhii Yashchuk
Serhii Yashchuk

Reputation: 1

in my case, recommendations with SUM(CASE WHEN Event Category = 'Game' and Event Action ='Game Session Started' THEN 1 ELSE 0 END) didn't work because basically, we need to mix dimensions with metrics...

in the best case it has to be something like this: CASE WHEN Event Category = 'Game' and Event Action ='Game Session Started' THEN {{UNIQUE EVENTS}} ELSE 0 END but it does not work because we mix Event Category / Event Action (dimensions) with calculated result {{UNIQUE EVENTS}} (metric) - maybe in the future, it will work...

To fix this task, I did next: 1 created 2 independent tables, each of them filtering by a specific event 2 blend data adding 3rd table with a date for the shared timeline 3 in blended data source - calculated SUM(Total Events (game start))-SUM(Total Events (game finished))

chart

data blending settings

filter for one table

Upvotes: 0

Andi Jankovič
Andi Jankovič

Reputation: 19

I think this may not be feasible to do, however as you requested:

Field 1 - SUM(CASE WHEN Event Category = 'Game' and Event Action ='Game Session Started' THEN 1 ELSE 0 END)

Field 2 - SUM(CASE WHEN Event Category = 'Game' and Event Action ='Game Session Finished' THEN 1 ELSE 0 END)

Field 3 - (Field 1 - Field 2)

Field 4 - Count(Event Category)

Picture

Upvotes: 0

Bobbylank
Bobbylank

Reputation: 1946

Try 

sum(case 
WHEN Event_Category = 'Game' and Event_Action='Game Session Started' THEN 'Total Events'
ELSE 0 END)
-
sum(case 
WHEN Event_Category = 'Game' and Event_Action='Game Session Finished' THEN 'Total Events'
ELSE 0 END)

You may need to split into 2 calculated metrics and then use a 3rd to minus the finished from the started.

Upvotes: 1

Related Questions