Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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))
Nope 😕 It does not take all values. Sometimes it leaves out some, and it shows the price based on the wrong eventtype
As u can see in the attached it sums randomly it looks like? sometimes it sums on Visit also.
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))
Many thanks! Youre good
Do you know if there is a faster way to do this? The expression is quite slow!
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).
How would you flag them in the load script based on the same logic? That an itemclick and buy happens within same session?
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)