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.

[Saryk]

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

[JediBabe]

No it is definitely not that - the Policy Expiry Date has to be after the Prior Month End

[JediBabe]

It is like the vPriorMonthEnd is coming through as a number, not as a date.  No matter what I do

[Saryk]

My bad, I read it as the opening of the '< >'.

[Saryk]

Do your dates in excel include hour data ? Qlik can't compare '09/01/2020' and '09/01/2020 11:44:00', or rather it won't match them.

Unless I need the hour data, I usually floor() dates to circumvent this.

[agigliotti]

maybe this:

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

[JediBabe]

Thanks.  I tried it but unfortunately it didn't work.  

[JediBabe]

The dates are coming from sql, and don't have the timestamp.  I just double-checked, and they are dates in the format YYYY-MM-DD coming from sql.

[agigliotti]

let's try with the below expression:

=Count
(
{ <

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