topic Re: Counting transactions after event in QlikView

Counting transactions after event

Mon, 16 Nov 2015 11:12:41 GMT

Hi community,

I would like to count the numnber of transactions occuring after a specific event for each person.

The data looks like this:

The event of interest is entered as a variable (v_Code). So if i enter 2 as event in v_code, i would like to have a result like this:

Person Number of Transactions after Code=2 (TAID)

1 3

2 1

Does anybody know how to solve this? I guess set-analysis is not possible. I tried asof-Tables, but cannot create them because of size (3 million transactions in table).

Any help appreciated.

Cheers Andreas.

Re: Counting transactions after event

sunny_talwar — Mon, 16 Nov 2015 11:23:13 GMT

May be this:

Dimesnion: PERSON

Expression: =Count({<CODE = {"$(='>' & $(v_Code))"}>}PERSON) or may be this:

=Count(If(CODE > $(v_Code), PERSON))

Re: Counting transactions after event

Mon, 16 Nov 2015 11:31:15 GMT

Thanks Sunny,

I tried it. Seems to work but only if a code is used that both Persons received. For example if i enter 1 for v_code it correctly returns 4 for Person 1 but also 2 for Person 2 although this Person hasn't received Code 1 at all.

Cheers

Andreas

Re: Counting transactions after event

Anonymous — Mon, 16 Nov 2015 11:46:30 GMT

try with Dimension Person with following expression:

Exp: count({<CODE={'>=$(=v_Code)'}>} Person)

make sure CODE should be in num format

Re: Counting transactions after event

swuehl — Mon, 16 Nov 2015 11:49:30 GMT

Or maybe

Dimension: Person

Expression: =Count({<CODE = {">=$(v_Code)"}>} TAID) -1

Expression:

=Count( AGGR( if( TAID > MIN(TOTAL<Person> {<Code = {'$(v_Code)'}>} TAID), TAID), PERSON, TAID))

edit: It's not really clear if Code of a TAID could be any Code, even lower than the previous codes and the code you are looking for. It seems that could be the case and that you just want to count any TAID after the first occurence of vCode, sorted by TAID. I believe, you would need something like my second expression then.

Re: Counting transactions after event

Kushal_Chawda — Mon, 16 Nov 2015 11:50:09 GMT

Create straight table

Dimension : Person

Expression : count({<Code ={"$(=v_Code)"}>}Person)

Re: Counting transactions after event

Mon, 16 Nov 2015 12:19:19 GMT

Thanks swuehl,

you are right, the codes are ordered by chance in the example. It is possible, that they are not ordered, so that a >= comparison would fail.

I tried your second solution, but it doesn't return anything.

Edit: Found it Person for TOTAL has to be also in upper case. But it is still not working. For code=2 it returns for Person1 4 and for Person2 = 1. Any idea why?

Re: Counting transactions after event

swuehl — Mon, 16 Nov 2015 12:57:21 GMT

Second expression seems to work ok also for v_Code = 1, just need to use the correct field names (QV is case sensitive):

=Count( AGGR( if( TAID > MIN(TOTAL<PERSON> {<CODE = {'$(v_Code)'}>} TAID), TAID), PERSON, TAID))

Re: Counting transactions after event

sunny_talwar — Mon, 16 Nov 2015 12:59:09 GMT

What do you expect to see when you v_Code = 1 for person 2?

Re: Counting transactions after event

Mon, 16 Nov 2015 13:02:24 GMT

It should be empty, because Person 2 didn't receive code 1.

Edit: Thanks Sunny. See answer of swuehl

Re: Counting transactions after event

Mon, 16 Nov 2015 13:05:52 GMT

Tried it again and found it working. Thanks so much swuehl. No I'm trying the reponse time for 3 million entries