Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Count({$<CalendarDate={"<=$(vPriorMonthEnd)"},DateType={'FKPolicyEffectiveDate'}>*$<CalendarDate={"<=$(vPriorMonthEnd)"},DateType={'FKPolicyExpiryDate'},PolicyStatusCode={'INF'}>}Distinct PolicyNumber)
No it is definitely not that - the Policy Expiry Date has to be after the Prior Month End
It is like the vPriorMonthEnd is coming through as a number, not as a date. No matter what I do
My bad, I read it as the opening of the '< >'.
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.
maybe this:
=Count({ <CalendarDate = {"<=Date($(vPriorMonthEnd), '$(DateFormat)')"},DateType = {'FKPolicyEffectiveDate'}>*<CalendarDate = {">=Date($(vPriorMonthEnd), '$(DateFormat)')"},DateType = {'FKPolicyExpiryDate'},PolicyStatusCode = {'INF'}> }Distinct PolicyNumber )
Thanks. I tried it but unfortunately it didn't work.
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.
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 )
