topic Re: Calculated Dimension - Multiple non-nested IFs in QlikView

Calculated Dimension - Multiple non-nested IFs

Thu, 26 Jun 2014 10:43:31 GMT

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

Re: Calculated Dimension - Multiple non-nested IFs

tresB — Thu, 26 Jun 2014 10:48:59 GMT

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)

Re: Calculated Dimension - Multiple non-nested IFs

Thu, 26 Jun 2014 11:16:26 GMT

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.