0

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

Andi Jankovič
  • 19
  • 2
  • 5

3 Answers3

1

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.

Bobbylank
  • 1,906
  • 7
  • 15
  • I created two calculated metrics (basically split your formula) and the another calculated metric (one minus the other) but the results is still empty. – Andi Jankovič Nov 29 '18 at 16:53
  • My first calculated field is: SUM(CASE WHEN Event Category = 'Game' and Event Action ='Game Session Started' THEN 1 ELSE 0 END) My 2nd calculated field is: SUM(CASE WHEN Event Category = 'Game' and Event Action ='Game Session Finished' THEN 1 ELSE 0 END) – Andi Jankovič Dec 06 '18 at 12:11
  • When i add this in the table, the number is only 1. – Andi Jankovič Dec 06 '18 at 12:33
  • If you add it to a table with the "Event Category" and "Event Action" dimensions and a count of one of those dimensions, what do you get? – Bobbylank Dec 06 '18 at 12:42
  • My table is then: Dimension1: Event Category Dimension2: Event Action Metric: Calculated Metric - SUM(CASE WHEN Event Category = 'Game' and Event Action ='Game Session Started' THEN 1 ELSE 0 END) - but as usual it shows just 1. – Andi Jankovič Dec 06 '18 at 13:43
  • Did you add the count() field? Can you pop up a picture too? – Bobbylank Dec 06 '18 at 14:31
  • Current status: 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: Test - Game Session Started-Test - Game Session Finished Where should I use the count? Not in the connection of sum, or ? Thanks for help! As far as I know, re-aggregating metrics is not possible in DataStudio – Andi Jankovič Dec 11 '18 at 15:53
  • Just add the count field to a table. So you'd have a table with dimensions - Event Category, Event Action and metrics - Field 1, field 2, field 3, count(event category) – Bobbylank Dec 11 '18 at 16:48
  • Okay, that helps. I've edited my answer, see if that helps. – Bobbylank Dec 17 '18 at 08:28
  • Just having a second look at the picture, it suggests your data is already rolled up into groups of Event Category and Event Action rather than at a hit level. Is that the case? – Bobbylank Dec 18 '18 at 12:47
  • All the events have same event category/event action, the only split is on event label (there were have different values). The edited version also shows the invalid formula. – Andi Jankovič Dec 18 '18 at 14:28
  • Doing this directly on a GA source gives "Sorry, calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values). Please check the aggregation types of the fields used in this formula. [Learn more](https://support.google.com/datastudio/answer/7569962#troubleshoot-calculated-fields&zippy=%2Cin-this-article)." – Harm Oct 20 '21 at 15:33
  • [This](https://stackoverflow.com/a/60882284/1913230) solved it for me – Harm Oct 20 '21 at 15:39
0

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

SimplGy
  • 20,079
  • 15
  • 107
  • 144
Andi Jankovič
  • 19
  • 2
  • 5
0

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

Serhii Yashchuk
  • 1