Find price in same session

[#1]

Hi,

I have a problem - See attached for example:

Basic thing:

I need the Price when there is an eventtype called Buy, but they also have an eventtype called Itemclick - They should be within same session.

As it is now it shows me the price for a visit - I dont want that - It should only be on the buys.

Example

RowID, CreateDate,EventType,Price, sessionid

1, 2014-02-02, itemclick, 0, 12121

2,2014-02-02,visit,203,12121

3,2014-02-02,buy,205,12121

4,2014-02-02,buy,202,12121

Then my price should be seen on both and total should be 407.

[Nicole-Smith]

Doesn't the solution from this post How to show sum based on a type in an event work for you?

=sum(aggr(if(substringcount(concat(EventType, ','),'itemclick')>0,sum({<EventType={'buy'}>}Price)),sessionid))

[#3]

Nope 😕 It does not take all values. Sometimes it leaves out some, and it shows the price based on the wrong eventtype

[#4]

As u can see in the attached it sums randomly it looks like? sometimes it sums on Visit also.

[Nicole-Smith · Accepted solution]

You've added more dimensions to that chart than are in the aggr(), so you need to also add those to the aggr(), then you have to use total in the concat() but still take into account the session:

=sum(aggr(if(substringcount(concat(total <SessionIDINT> EventType, ','),'Itemclick')>0,sum({<EventType={'Buy'}>}PriceCleansed)),Hour2,Minute2,Date_Clean,SessionIDINT,EventType))

[#6]

Many thanks! Youre good

[#7]

Do you know if there is a faster way to do this? The expression is quite slow!

[Nicole-Smith]

Flagging the values you want to add in the load script, and then just using plain set analysis to get the sum (instead of the if statement and the aggr).

[#9]

How would you flag them in the load script based on the same logic? That an itemclick and buy happens within same session?

[Nicole-Smith]

Load script (Hej was the name of the table in your example--if it's actually a different name in the real file, you'll need to change the name here):

LEFT JOIN (Hej)

LOAD DISTINCT

    SessionIDINT,

    'Buy' as EventType,

    1 as RevenueFlag

RESIDENT Hej

WHERE match(EventType,'Itemclick');

Then the expression on your chart is simply:

sum({<RevenueFlag={1}>}PriceCleansed)