Counting transactions after event

[#1]

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.

[sunny_talwar]

May be this:

Dimesnion: PERSON

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

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

[#3]

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

[Anonymous]

try with Dimension Person with following expression:

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

make sure CODE should be in num format

[swuehl]

Or maybe

Dimension: Person

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

or

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.

[Kushal_Chawda]

Create straight table

Dimension : Person

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

[#7]

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?

[swuehl · Accepted solution]

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

[sunny_talwar]

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

[#10]

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

Edit: Thanks Sunny. See answer of swuehl