Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Give this a shot
Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {"$(='>=' & Date(MakeDate(2017, 8, 15), 'DD-MMM-YY'))"}>} DISTINCT POLICY_NO)
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)
use below
Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {">=$(=makedate(2017,8,15)) <= $(=Max(TRAN_DATE))"},RENEWED_DATE = {"<=$(Max(TRAN_DATE))"}>} DISTINCT POLICY_NO)
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
Does RENEWED_DATE and TRAN_DATE are associate each?
Hi Anil
I will check and come back
have you tried my suggestion?
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...
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
Give this a shot
Count({<RENEWED_PREMIUM = {'>0'}, RENEWED_DATE = {"$(='>=' & Date(MakeDate(2017, 8, 15), 'DD-MMM-YY'))"}>} DISTINCT POLICY_NO)