Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try with:
sum({$ <INVOICE_TYPE={'STANDARD'}, LINE_TYPE_LOOKUP_CODE={'ITEM'}>} If(IsNull(Line_lookup_code), AMOUNT_PAID, 0))
let me know
Hi,
=sum({<INVOICE_TYPE ={'STANDERED'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},Line_lookup_code={'NULL'}
>}AMOUNT_PAID)
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
sum({<INVOICE_TYPE ={'STANDARD'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},LINE_LOOKUP_CODE={''}>}AMOUNT_PAID)
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
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)
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
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.
Thanks Jeremy , its working now