topic Conditional Expression with a filter in QlikView

Conditional Expression with a filter

Wed, 16 Nov 2011 07:17:00 GMT

Hello

Can someone please help me with my formula in a pivot table?

= if(Account <> 'GP%', sum(if(Type = 'Actual', Amount)), if (Account = 'GP%',

and this is where I need help.

If Account = GP% then the formula for this is sum(AmountGP)/sum(AmountInv) however it needs to be filtered on Type = 'Actual' in the first place.

As far as I can work out to perform this I need to have the sum outside the brackets that include the Type. This however gives me the wrong amount though as it is summing together the product of hte formula when I need it to work the other way.

How can I filter on Type = 'Actual' and then perform my formula?

Thankyou in advance,

Conditional Expression with a filter

vgutkovsky — Wed, 16 Nov 2011 16:21:09 GMT

So you're trying to check for the accounts that start with 'GP'? Try this:

if(left(Account,2) <> 'GP',

sum({<Type={'Actual'}>} Amount),

sum({<Type={'Actual'}>} AmountGP)/sum({<Type={'Actual'}>} AmountInv)

)

Regards,

Vlad

Conditional Expression with a filter

Thu, 01 Dec 2011 00:01:47 GMT

Vlad

Thankyou so much that was exactly what I needed!!! BRILLIANT!!

Also sorry it took so long to get back to you - got pulled onto another project.

Thanks again.

Fiona,