Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
JediBabe
New Contributor

Set Analysis Not Working with Dates

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.

15 Replies
Highlighted
Partner
Partner

Re: Set Analysis Not Working with Dates

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

Highlighted
JediBabe
New Contributor

Re: Set Analysis Not Working with Dates

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

Highlighted
JediBabe
New Contributor

Re: Set Analysis Not Working with Dates

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

Partner
Partner

Re: Set Analysis Not Working with Dates

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

Highlighted
Partner
Partner

Re: Set Analysis Not Working with Dates

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.

Highlighted
Partner
Partner

Re: Set Analysis Not Working with Dates

maybe this:

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

Highlighted
JediBabe
New Contributor

Re: Set Analysis Not Working with Dates

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

Highlighted
JediBabe
New Contributor

Re: Set Analysis Not Working with Dates

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.

Highlighted
Partner
Partner

Re: Set Analysis Not Working with Dates

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 )