SET ANALYSIS EXPRESSION

[yadav_anil782]

Dear comunity,

plz helpt  to write correct script for this condision-  sum of "AMOUNT_PAID"

WITH CONDISION

INVOICE_TYPE                     =STANDERED

LINE_TYPE_LOOKUP_CODE =ITEM

Line_lookup_code                    = NULL

AMOUNT_PAID	INVOICE_TYPE	LINE_TYPE_LOOKUP_CODE	Line_lookup_code
900	STANDARD	freight	india
3600	debit	ITEM
6000	STANDARD	freight	india
10000	debit	ITEM
20400	debit	freight
25242	STANDARD	ITEM	india
461173	STANDARD	freight
1699999	STANDARD	ITEM	india

[alexandros17]

Try with: 
sum({$ <INVOICE_TYPE={'STANDARD'}, LINE_TYPE_LOOKUP_CODE={'ITEM'}>} If(IsNull(Line_lookup_code), AMOUNT_PAID, 0))

let me know

[#3]

Hi,

=sum({<INVOICE_TYPE ={'STANDERED'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},Line_lookup_code={'NULL'}

>}AMOUNT_PAID)

[Anonymous]

Hi Anil,

Load

sum(AMOUNT_PAID) as AMOUNT_PAID ,

INVOICE_TYPE,

LINE_TYPE_LOOKUP_CODE

from...

where

INVOICE_TYPE = {'STANDARD'} and LINE_TYPE_LOOKUP_CODE = {'ITEM'}  and  len(trim(Line_lookup_code)) <> 0

group by INVOICE_TYPE,LINE_TYPE_LOOKUP_CODE

Regards

Neetha

[Anonymous]

sum({<INVOICE_TYPE ={'STANDARD'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},LINE_LOOKUP_CODE={''}>}AMOUNT_PAID)

[#6]

Hi try this

=sum({<INVOICE_TYPE ={'STANDERED'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},Line_lookup_code={"=len(trim(Line_lookup_code))<=0"}>}AMOUNT_PAID)

thanks

manju

[tresesco]

If I take your sample data, it doesn't really contain a NULL(which set analysis can't catch and not selectable in QV, but yours is selectable here ).

You can try :

=Sum({<INVOICE_TYPE={STANDARD},LINE_TYPE_LOOKUP_CODE={ITEM},Line_lookup_code-={''}>} AMOUNT_PAID)

[sasiparupudi1]

I think you need two statements

may be try this

sum({$ <INVOICE_TYPE={'STANDARD'}, LINE_TYPE_LOOKUP_CODE={'ITEM'}>}  AMOUNT_PAID)+sum({$ <Line_lookup_code ={"=len(trim(Line_lookup_code))=0"},LINE_TYPE_LOOKUP_CODE-={'ITEM'}>}  AMOUNT_PAID)

the statement might show error line but it should still work .

hth

Sasi

[Anonymous]

Hi Anil, the difficult part here is the NULL for Line_lookup_code. It is not easy to simply use set analysis for this. You can use an IF statement as follows -

=sum(if(INVOICE_TYPE = 'STANDARD' and LINE_TYPE_LOOKUP_CODE = 'ITEM' and isnull( Line_lookup_code), AMOUNT_PAID))

Or you can simply set your null values for the field in script and use this in your set analysis  - you can use set null values or - as follows:-

if(isnull(Line_lookup_code),'NULL',Line_lookup_code) as Line_lookup_code,

then you can use the new value in your set analysis as follows:-

=sum({<INVOICE_TYPE = {'STANDARD'},LINE_TYPE_LOOKUP_CODE = {'ITEM'},Line_lookup_code ={'NULL'}>} AMOUNT_PAID)

Hope this helps.

[yadav_anil782]

Thanks Jeremy , its working now