topic Re: Count if dateA is before dateB (incl. variable) in QlikView

Count if dateA is before dateB (incl. variable)

Wed, 22 Mar 2017 20:16:01 GMT

I have an expression that works well. What it does, is, it counts the invoices “Paid ontime” and the “Paid ontime” is already set during import.

It looks like this:

Count({$<[PAYMENTS.PAYMENT_PERFORMANCE] = {'Paid ontime'}>}DISTINCT([PAYMENTS.INVOICE_ID]))

Now I am trying to do something similar but more enhanced.

I want to count invoices where the payment date is before the due date including a tolerance which is a numeric value (variable) that can be set in a input box.

I have tried it like this but something is not right.

Count({$<[PAYMENTS.PAYMENT_DATE] = {>= Date(Date(Num(PAYMENTS.INVOICE_DUE_DATE)+v_ontime_tolerance)) }>}DISTINCT([PAYMENTS.INVOICE_ID]))

Can anyone indicate what's wrong?

Thanks,

Benny

Re: Count if dateA is before dateB (incl. variable)

Wed, 22 Mar 2017 20:35:37 GMT

My bad, it is actually the other way round but still does not work:

Count({$<[PAYMENTS.PAYMENT_DATE] = {<= Date(Date(Num(PAYMENTS.INVOICE_DUE_DATE)+v_ontime_tolerance)) }>}DISTINCT([PAYMENTS.INVOICE_ID]))

Re: Count if dateA is before dateB (incl. variable)

Anil_Babu_Samineni — Thu, 23 Mar 2017 01:56:37 GMT

Would you able share a application, Please. May be this?

Count({$<[PAYMENTS.PAYMENT_DATE] = {"<=$(=Date(Date(Num(PAYMENTS.INVOICE_DUE_DATE)+ $(v_ontime_tolerance))))"}>}DISTINCT([PAYMENTS.INVOICE_ID]))

Please describe v_ontime_tolerance

Count if dateA is before dateB (incl. variable)

Re: Count if dateA is before dateB (incl. variable)

sunny_talwar — Thu, 23 Mar 2017 09:29:35 GMT

May be just use an if statement

Count({$<[PAYMENTS.PAYMENT_PERFORMANCE] = {'Paid ontime'}>} DISTINCT If([PAYMENTS.PAYMENT_DATE] >= (PAYMENTS.INVOICE_DUE_DATE+v_ontime_tolerance), [PAYMENTS.INVOICE_ID]))