Set analysis, date range

[krissy_s]

Hi All

I would like to count all the policies which have an expiry date in the current month or proceeding 2 months. Here is what I have come up with but it does not seem to be working. Can anyone see what I have done wrong please?

Count({$<[Expiry Date]={"$(='>=' & monthstart(Today()) & '<=' & monthend(addmonths(today(),2)))"}> } distinct[Policy Number])

Many thanks

[tresesco]

Date format could be an issue. Have you checked that? Also when you use monthend(), addmonths() becomes a redundant because you can pass another argument in the monthend() itself to achieve the same.

[Channa]

=Sum({$<[Expiry Date]={">=$(=MonthStart(Today()))<$(=monthend(Today(),2))"}>}distinct[Policy Number])

make sure ur Expire date format is same as  =MonthStart(Today())

try this

[Vegar]

It could be as @tresesco suggests. It is important that [Expiry Date] is an date not only an integer or string that looks like a date? What happens if you do Dayname([Expiry Date]) ?

I usally write these expressions a slightly different, you could try my way to see if it helps. I would write your expression like this:

Count({$<[Expiry Date]={">=$(=monthstart(Today())<=monthend(addmonths(today(),2)))"}> } distinct[Policy Number])