Set Analysis Not Working with Dates

[JediBabe]

I have a set analysis equation to count the number of policies live, based on a set date.  This date is loaded in the script, and I have done similar in other reports but this is not working and I can't see why.  The set expression is:

Count({$<CalendarDate={"<=$(vPriorMonthEnd)"},DateType={'FKPolicyEffectiveDate'}>*
$<CalendarDate={">=$(vPriorMonthEnd)"},DateType={'FKPolicyExpiryDate'},PolicyStatusCode={'INF'}>}Distinct PolicyNumber)

vPriorMonthEnd is:

Let vPriorMonthEnd=MonthEnd(AddMonths('$(vMaxDate)',-1));

Nothing I try seems to be working.

[sagarjagga]

May be , you have to format the value coming from variable again

Count({$<CalendarDate={"<=$(=Date($(vPriorMonthEnd),'MM/DD/YYYY'))"},DateType={'FKPolicyEffectiveDate'}>*
$<CalendarDate={">=$(=Date($(vPriorMonthEnd),'MM/DD/YYYY'))"},DateType={'FKPolicyExpiryDate'},PolicyStatusCode={'INF'}>}Distinct PolicyNumber)

[JediBabe]

Well, it gave me dashes instead of 0.  

I'm convinced that because it was originally 0, that it is evaluating the date wrong so that there are no policies that match the criteria.  When I put the variable into a measure on its own just as $(vPriorMonthEnd) then it evaluates to 30/12/1899.  If I put single quotes around the whole thing it evaulates properly.  

[agigliotti]

did you try to replace the vPriorMonthEnd variable with its value in set analysis to see if you get the expected result ?

[JediBabe]

I tried, but it wasn't working and I think it is my lack of knowledge of how to write that.  Could you recommend how to do that?

[agigliotti]

how can you say it's doesn't working?
what's your expected result for that expression?
what's the date format of CalendarDate field ?
what's the vPriorMonthEnd variable value?

[JediBabe]

Ok, some some background then:

This was working with the following original expression:

Count({$<PolicyEffectiveDate={"<=$(vPriorMonthEnd)"},PolicyExpiryDate={">$(vPriorMonthEnd)"},PolicyStatusCode={'INF'}>}Distinct PolicyNumber)

However to follow out new standards, I needed to move it to using a datelink table, which joins FKPolicyEffectiveDate and FKPolicyExpiryDate to a date table.  These are text fields which mirror the data in PolicyEffectiveDate and PolicyExpiryDate, in the format YYYYMMDD.  We have done this lots of times in other apps, and it works fine.

So this joins to a date table, and CalendarDate is then a date field in the format YYYY-MM-DD, and we then use the original expression I posted.  But for some reason it isn't working.

The vPriorMonthEnd is just an expression to give the end of the prior month based on the last transaction date in the table (rather than today, in case of a failed data load).