topic Re: Expression Between two dates in QlikView

Expression Between two dates

upaliwije — Mon, 21 Aug 2017 10:21:33 GMT

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

I want above expression to return data between these two dates RENEWED_DATE = {'>=15-Aug-2017'},RENEWED_DATE = {"<=$(Max(TRAN_DATE))" But it returns data even prior 15th Aug 2017 I may have made mistake. Pls help me to correct it

Re: Expression Between two dates

Anil_Babu_Samineni — Mon, 21 Aug 2017 10:32:29 GMT

One best option this?

If(RENEWED_DATE >= '15-Aug-2017' and RENEWED_DATE <= Max(TOTAL TRAN_DATE),1,0) as Flag

Set analysis should like below

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

Create variable for that static date and use below, I don't think whether this will work. But attempt is good

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

Re: Expression Between two dates

Kushal_Chawda — Mon, 21 Aug 2017 10:51:50 GMT

use below

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

Re: Expression Between two dates

upaliwije — Mon, 21 Aug 2017 10:56:23 GMT

Hi Anil

I can not use your 1st Option since TRAN_DATE will change daily . I tried your 2nd Option but It does not work

My expression is

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

My variable is like this

Your advice is appreciated

Re: Expression Between two dates

Anil_Babu_Samineni — Mon, 21 Aug 2017 10:59:20 GMT

Does RENEWED_DATE and TRAN_DATE are associate each?

Re: Expression Between two dates

upaliwije — Mon, 21 Aug 2017 11:17:15 GMT

Hi Anil

I will check and come back

Re: Expression Between two dates

Kushal_Chawda — Mon, 21 Aug 2017 11:19:02 GMT

have you tried my suggestion?

Re: Expression Between two dates

sunny_talwar — Mon, 21 Aug 2017 11:28:27 GMT

Not sure, but may be formatting might be an issue as well

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {"$(='>=' & Date(MakeDate(2017, 8, 15), 'DD-MMM-YYYY') & '<=' & Date(Max(TRANS_DATE), 'DD-MMM-YYYY'))"}>} DISTINCT POLICY_NO)

Here I am assuming that RENEWED_DATE is in DD-MMM-YYYY format, if this is not true, then change the date format within the set analysis accordingly...

Re: Expression Between two dates

upaliwije — Mon, 21 Aug 2017 11:29:58 GMT

Dear Anil & Kushal

I have changed the TRAN_DATE with RENEWED_DATE and the expression is as follows

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {">=$(=makedate(2017,8,15)) <= $(=Max(RENEWED_DATE))"},RENEWED_DATE = {"<=$(Max(RENEWED_DATE))"}>} DISTINCT POLICY_NO)

Then the Result is shown below

Re: Expression Between two dates

sunny_talwar — Mon, 21 Aug 2017 11:31:16 GMT

Give this a shot

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {"$(='>=' & Date(MakeDate(2017, 8, 15), 'DD-MMM-YY'))"}>} DISTINCT POLICY_NO)

Re: Expression Between two dates

Kushal_Chawda — Mon, 21 Aug 2017 11:33:40 GMT

below should work

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {">=$(=makedate(2017,8,15)) <= $(=Max(RENEWED_DATE))"}>} DISTINCT POLICY_NO)

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {">=$(=date(makedate(2017,8,15),'DD-MMM-YY')) <= $(=date(Max(RENEWED_DATE),'DD-MMM-YY'))"}>} DISTINCT POLICY_NO)

Re: Expression Between two dates

sunny_talwar — Mon, 21 Aug 2017 11:47:10 GMT

It might be better to use this as provided by kush141087

Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {">=$(=Date(MakeDate(2017,8,15),'DD-MMM-YY')) <= $(=Date(Max(RENEWED_DATE),'DD-MMM-YY'))"}>} DISTINCT POLICY_NO)