topic Re: Find price in same session in QlikView

Find price in same session

Tue, 11 Feb 2014 19:52:59 GMT

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.

Re: Find price in same session

Nicole-Smith — Tue, 11 Feb 2014 19:55:58 GMT

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

Re: Find price in same session

Tue, 11 Feb 2014 19:57:38 GMT

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

Re: Find price in same session

Tue, 11 Feb 2014 19:58:57 GMT

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

Re: Re: Find price in same session

Nicole-Smith — Tue, 11 Feb 2014 20:11:41 GMT

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

Re: Find price in same session

Tue, 11 Feb 2014 20:40:27 GMT

Many thanks! Youre good

Re: Find price in same session

Tue, 11 Feb 2014 21:07:01 GMT

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

Re: Find price in same session

Nicole-Smith — Tue, 11 Feb 2014 21:09:41 GMT

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

Re: Find price in same session

Tue, 11 Feb 2014 21:12:41 GMT

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

Re: Re: Find price in same session

Nicole-Smith — Wed, 12 Feb 2014 14:18:09 GMT

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)