topic Re: How to write expression in Pivot table in QlikView

How to write expression in Pivot table

Wed, 20 Nov 2013 12:55:43 GMT

Hi,

I need to get the values of Amount when

Amount>0 and Domain='RR' and Type in ('old',New') and ID=56432

here Amount,Domain,Type are from Sales table where ID is from Account table.

could anyone help me please how to write expression to get Amount.

Thanks.

Re: How to write expression in Pivot table

MK_QSL — Wed, 20 Nov 2013 13:02:40 GMT

IF(SUM({<Domain = {‘RR’}>}, Type = {<’old’,’New’>, ID = {‘56432’}}Amount)>0, SUM({<Domain = {‘RR’}>}, Type = {<’old’,’New’>, ID = {‘56432’}}Amount))

Considering that there is a common filed between both table. may be ID or Account ID

Re: How to write expression in Pivot table

Wed, 20 Nov 2013 13:08:30 GMT

Thanks and when I am usin this it is showing error at

Type = {<’old’,’New’>,

is there anything I need to change?

Re: How to write expression in Pivot table

MK_QSL — Wed, 20 Nov 2013 13:11:23 GMT

IF(SUM({<Domain = {‘RR’}, Type = {’old’,’New’}, ID = {‘56432’}>}Amount)>0, SUM({<Domain = {‘RR’}, Type = {’old’,’New’}, ID = {‘56432’}>}Amount))

Re: How to write expression in Pivot table

Wed, 20 Nov 2013 13:15:58 GMT

Thanks and still it is showing same error in the same place please help as I stucked here.

Re: How to write expression in Pivot table

MK_QSL — Wed, 20 Nov 2013 13:16:46 GMT

what is your dimension? Can you upload sample data?

Re: How to write expression in Pivot table

jcoggon — Wed, 20 Nov 2013 13:18:11 GMT

Hi Amelia,

You could also try SUM({<Amount = {">0"}, Domain = {"RR"}, Type = {"old","New"}, ID = {"56432"}>}Amount)

Re: How to write expression in Pivot table

Wed, 20 Nov 2013 13:18:33 GMT

the dimension is employee from Account table

Re: How to write expression in Pivot table

Wed, 20 Nov 2013 13:21:29 GMT

Thanks and hoe can I change if want to get Amount instaed of Sum(Amount) with same requirements.

Please could you send me the expression.

Re: How to write expression in Pivot table

MK_QSL — Wed, 20 Nov 2013 13:26:21 GMT

This should work...!!!

IF(SUM({<Domain = {‘RR’}, Type = {’old’,’New’}, ID = {‘56432’}>}Amount)>0, SUM({<Domain = {‘RR’}, Type = {’old’,’New’}, ID = {‘56432’}>}Amount))

Re: How to write expression in Pivot table

jcoggon — Wed, 20 Nov 2013 13:28:32 GMT

Could you maybe explain a little more what you are trying to do or upload a sample app.

Re: How to write expression in Pivot table

Wed, 20 Nov 2013 13:29:50 GMT

Thanks and will the same works for Amount instead of Sum(Amount)?

Re: How to write expression in Pivot table

MK_QSL — Wed, 20 Nov 2013 13:31:06 GMT

It's so simple still confusing. Could you pleas upload file ?

Re: How to write expression in Pivot table

thomaswrieck — Wed, 20 Nov 2013 15:09:44 GMT

Not really clear what you would like to achieve ...

If you just want to sum up Amount matching your criteria you could use :

sum( {<Domain={'RR'}, Type={'old', 'new'}, ID={1}>} Amount)

Here, there's no need to exclude 0 amounts ...

If you just want to show data in a table matching your criteria, the only thing i could get to work is using a bookmark.

Just check the qvw ... and choose the available bookmark ...

it has been created by using a search box and entering the following :

=Amount<>0 and ID=1 and (Type='new' or Type='old') and Domain='RR'

Then hit enter and save this as a bookmark.