topic Re: multiple where clause in expression in QlikView

multiple where clause in expression

swiftfahad — Tue, 25 Jul 2017 09:20:11 GMT

hi gurs!!

1) I am taking to_date from user through calendar and storing in variable V_todate

2) I have invoice id that send to customer against which customer made payments. (Invoice_id,invoice_date)

3) payments are made through vouchers. (voucher_id, voucher_date)

i am showing the sum of customer payments made against invoice through expression

=sum(aggr(alt(sum({&<voucher_date={"<=$(v_todate)"}>}received_amt),0),cust_id,inv_id)

this working fine for me shows all the receivce amount against voucher received before to_date.

in some scenario customer pay in advance so voucher date become less then invoice date.

so i wana skip such invoice where voucher date are smaller then invoice date in the above expression.

i am trying

=sum(aggr(alt(sum({&<voucher_date={"<=$(v_todate)"},Voucher_date>invoice_date>}received_amt),0),cust_id,inv_id)

giving error......

plz help how to incorporate two checks voucher_date < invoice_date and voucher date<=v_todate

Re: multiple where clause in expression

Kushal_Chawda — Tue, 25 Jul 2017 09:36:33 GMT

I think you also need to add the condition in Aggr as well to work the expression properly

=sum({<voucher_date={"=voucher_date>invoice_date"},voucher_date={"<=$(v_todate)"}>}aggr(alt(sum({<voucher_date={"=voucher_date>invoice_date"},voucher_date={"<=$(v_todate)"}>}received_amt),0),cust_id,inv_id)

Re: multiple where clause in expression

mangalsk — Tue, 25 Jul 2017 10:09:00 GMT

=sum(aggr(alt(sum({&<voucher_date={"<=$(v_todate)"},voucher_date={"<invoice_date"}>}received_amt),0),cust_id,inv_id)