How To show sum after nested calculation using beast mode

vgupta

Hi

I have a dataset where values are like below:

SessionId ActivityType ActivityTimeStamp Company

1001 Play 2022-04-18T04:28:12 ABC

1001 Play 2022-04-18T04:28:13 ABC

1001 Read 2022-04-18T04:28:14 ABC

1002 Play 2022-04-18T04:30:10 ABC

1002 Play 2022-04-18T04:30:15 ABC

1003 Play 2022-04-18T04:40:15 XYZ

1003 Play 2022-04-18T04:40:25 XYZ

1003 watch 2022-04-18T04:40:30 XYZ

I want to calculate sum of time spent in all sessions for each company e.g. time spent in 1001 => 2 secs, in 1002 = 5 secs so total 7 secs for company 'ABC'

I want o create a table card and do this calculation in beast mode.

Company TotalTimeSpentInSecs

ABC 7

XYZ 10

I am trying to achieve it like this but getting error , Function cannot be used inside of another aggregate:

SUM(SUM(TIME_TO_SEC(TIMEDIFF(Max(CASE when `ActivityType`='Play' OR `ActivityType`='Read' then `ActivityTimeStamp` end),MIN(CASE when `ActivityType`='Play' OR `ActivityType`='Read' then `ActivityTimeStamp` end)))) over (partition by `SessionId`))

Can anyone help here please ?