topic Re: Expression with date in QlikView

Expression with date

upaliwije — Mon, 21 Aug 2017 16:36:06 GMT

Dear Friends

I have following Expression which is working well.

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {"$(=Max(TRAN_DATE))"}>} DISTINCT POLICY_NO)

but when I add the part in red the following expression does not work

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {"$(=Max(TRAN_DATE))"},END_DATE = {"$(=Max(TRAN_DATE)-1, 'DD-MMM-YY')"}>} DISTINCT POLICY_NO)

Pls help me

antoniotiman — Mon, 21 Aug 2017 16:40:13 GMT

END_DATE = {"$(=Date(Max(TRAN_DATE)-1, 'DD-MMM-YY'))"}

upaliwije — Mon, 21 Aug 2017 16:47:59 GMT

Hi Antonio

Still the result is 0

sunny_talwar — Mon, 21 Aug 2017 16:49:30 GMT

END_DATE is in DD-MMM-YY format?

upaliwije — Mon, 21 Aug 2017 16:55:15 GMT

Yes Sunny

sunny_talwar — Mon, 21 Aug 2017 16:58:36 GMT

A single POLICY_NO can have single TRANS_DATE or multiple TRANS_DATE?

upaliwije — Mon, 21 Aug 2017 17:01:29 GMT

Basically a single TRAN_DATE but sometimes multiple dates can occur

sunny_talwar — Mon, 21 Aug 2017 17:10:27 GMT

In you screenshot above, do you have Date(Max(TRANS_DATE), 'DD-MMM-YYYY') = '15-Aug-2017'?

upaliwije — Mon, 21 Aug 2017 17:13:49 GMT

Yes Sunny

Anil_Babu_Samineni — Tue, 22 Aug 2017 01:04:06 GMT

Try to split and check where you are not getting. Like this?

Count({<RENEWED_PREMIUM = {'>0'}>} DISTINCT POLICY_NO)

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {'$(=Max(TRAN_DATE))'}>} DISTINCT POLICY_NO)

......