topic Re: Expression Data Exclude in QlikView

Expression Data Exclude

MalcolmCICWF — Fri, 30 May 2014 20:50:36 GMT

I currently am summing and creating some numbers from an amount field. I noticed my sums were coming out too high and I finally realized I was including some amounts I should not be. Below is the current Expression I have created to get what I need.

(row_fields = 'Gross IRR' or row_fields = 'Net IRR', num(sum(BL_Value)/sum(investment), '#,###.##%'),num(sum(amount),'#,##0'))

I have added the below underlined to try an exclude data matching these field requirements, I thought I placed it correctly within the function, the editor says "Expression OK", but it is not removing what I had expected. I would like to know if I am placing this correctly or is there another way I can write this? Is this the best way to exclude this data specific to this chart? I need the data brought in for another chart.

(row_fields = 'Gross IRR' or row_fields = 'Net IRR', num(sum(BL_Value)/sum(investment), '#,###.##%'),
if(portfpool <> 'ONETIME' or portfpool <> 'MICROLN' or portfpool <> 'CONSOLID' or portfpool <> 'CIRCAP', num(sum(amount),'#,##0')))

Re: Expression Data Exclude

Fri, 30 May 2014 21:10:48 GMT

Jeremy,

it may be difficult to check expression without example with data model and some data....

regards

Re: Expression Data Exclude

MalcolmCICWF — Fri, 30 May 2014 21:33:10 GMT

Well I wrote that in hopes someone could let me know if it looked correct, but I will include a data model...

Re: Expression Data Exclude

Anonymous — Fri, 30 May 2014 21:51:07 GMT

Hello, Jeremy.

Try this way:

if(row_fields = 'Gross IRR' or row_fields = 'Net IRR', num(sum(BL_Value)/sum(investment), '#,###.##%'),
num(sum({$<portfpool -= {'ONETIME', 'MICROLN', 'CONSOLID', 'CIRCAP'}>} amount),'#,##0')))

The syntax check you say it's wrong an -=, but it should work anyway.

Cheers.

Re: Expression Data Exclude

MalcolmCICWF — Mon, 02 Jun 2014 14:04:17 GMT

This actually worked very well, thanks Bruno!

Re: Expression Data Exclude

Anonymous — Mon, 02 Jun 2014 14:07:31 GMT

You're welcome, Jeremy. Glad I could help.