Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Can you explain why you did like you did and what it does?
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.