Expression Between two dates

[upaliwije]

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

[Anil_Babu_Samineni]

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)

OR

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)

[Kushal_Chawda]

use below

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

[upaliwije]

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

[Anil_Babu_Samineni]

Does RENEWED_DATE and TRAN_DATE are associate each?

[upaliwije]

Hi Anil

I will check and come back

[Kushal_Chawda]

have you tried my suggestion?

[sunny_talwar]

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...

[upaliwije]

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

[sunny_talwar · Accepted solution]

Give this a shot

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