Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Assume the following data:
Player:
signup_month
account_id
Transactions:
transaction_date
account_id
number_of_deposits
I need to get a table in the following format:
Number of deposits:
0+ 1,000
1+ 500
2+ 200
This is read as, there are 1,000 customers, of whom 500 made 1 or more deposits, 200 made 2 or more deposits, etc.
Obviously, the 2+ is a subset of the 1+ group.
I get close to a solution by using a calculated dimension of the form:
=AGGR( if(sum(number_of_deposits) = 0, '0',
if(sum(number_of_deposits) = 1, '1',
if(sum(number_of_deposits) >=2, '2+', 'Error'), account_id)
However with this I can only see those who made exactly 0 deposits, exactly 1 deposits etc. giving me:
0 500
1 300
2+ 200
When I change the above '=' to '>=', then I get:
0+ 1,000
and nothing else, as QV stops evaluating the IF on the first clause. This stands to reason as it is an IF..THEN..ELSE statement.
Any ideas on how I can solve this problem?
Thanks
Write If with reverse condition(check bigger number first) like:
=AGGR( if(sum(number_of_deposits) >= 2, '2+',
if(sum(number_of_deposits) >= 1, '1+',
if(sum(number_of_deposits) >=0, '0+', 'Error'), account_id)
Hi, thanks for replying, but that still doesn't work.
I forgot to mention in my original post that the expression I am then using is a simple, COUNT( account_id).
I guess it doesn't work because any account_id is only being placed in one of the slots of 0+ or 1+, etc. Whereas I need the same account_id to be present in 0+ and 1+ if he has made 2 deposits.