topic Re: How to show sum based on a type in an event in QlikView

How to show sum based on a type in an event

Thu, 06 Feb 2014 14:13:59 GMT

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

Re: How to show sum based on a type in an event

Nicole-Smith — Thu, 06 Feb 2014 15:31:23 GMT

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.

Re: How to show sum based on a type in an event

Thu, 06 Feb 2014 15:48:42 GMT

Can you explain why you did like you did and what it does?

Re: How to show sum based on a type in an event

Nicole-Smith — Thu, 06 Feb 2014 15:54:33 GMT

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.