Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Find price in same session

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.

1 Solution

Accepted Solutions
Nicole-Smith

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))

View solution in original post

9 Replies
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))

Not applicable
Author

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

Not applicable
Author

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

Nicole-Smith

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))

Not applicable
Author

Many thanks! Youre good

Not applicable
Author

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).

Not applicable
Author

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)