How to show sum based on a type in an event

[#1]

Lets say i have these two scenarios:

This one where i buy in the same session

ID,     Session, Type, Price, Productid, createdate

7          3     buy      400          4     2014-01-18 11:00:00.00

6          2     visit     100          1     2014-01-17 11:00:00.00

5          2     visit     50          3     2014-01-15 11:00:00.00

4          1     buy     100          1     2014-01-14 12:06:00.00

3          1     visit     200          2     2014-01-14 12:05:00.00

2          1     visit     100          1     2014-01-14 12:02:00.00

1          1     Click     0          1         2014-01-14 12:00:00.00

How can i show in a graph how mouch revenue (sum(price) there has been when there has been a click also? (same session)

The sum should show 100 for the 14

[Nicole-Smith · Accepted solution]

Something like this should work:

=sum(aggr(if(substringcount(concat(Type, ','),'Click')>0,sum({<Type={'buy'}>}Price)),Session))

I've also attached a working example.

[#3]

Can you explain why you did like you did and what it does?

[Nicole-Smith]

The aggr() uses Session as a "dimension".  Then we have the if statement with the substringcount() and concat().  The concat() gets a list of all Types that happened during a session, and the substringcount() makes sure one of these Types is a click.  Then we sum the Price for the Type but only if there was a Type = click (the substringcount() part).  The outer sum is just to add everything together.