Set Analysis with Variables

[mrainey18]

I am trying to figure out this set analysis and I cannot get it to work.  Basically I am trying to count a set of distinct values if they fall in the past 7 days and if their status is delivered:

count(distinct {<CalendarDate = {'>=$(=$(vSevenDaysAgo))'}, Status = {'DELIVERED'}>} OrderNumber)

This works by itself:

count(distinct {<Status = {'DELIVERED'}>} OrderNumber)

But when I add in the date it comes up with Zero.

[bharathadde]

Try this

count(distinct {<CalendarDate = {'>=$(=vSevenDaysAgo)'}, Status = {'DELIVERED'}>} OrderNumber)

[mrainey18]

That still gives me 0 and it should be showing 30.

[bharathadde]

Can you share sample data or .qvw file?

[mrainey18]

No, unfortunately I can not. 

[anseglko · Accepted solution]

For some weird reason I got it working by replacing the single quotes with double quotes..

count(distinct {<CalendarDate = {">=$(=vSevenDaysAgo)"}, Status = {'DELIVERED'}>} OrderNumber)

[anseglko · Accepted solution]

count(distinct {<CalendarDate = {">=$(=vSevenDaysAgo)"}, Status = {'DELIVERED'}>} OrderNumber)

[tresesco]

---

@anseglko wrote:

For some stupid reason I got it working by replacing the single quotes with double quotes..

count(distinct {<CalendarDate = {">=$(=vSevenDaysAgo)"}, Status = {'DELIVERED'}>} OrderNumber)

---

It has a strong reason.

With new qlik engine,

single quotes are used only for exact match in comparison

double quotes are used for search expression

Check this: Quotes in Set Analysis

[tresesco]

count(distinct {<CalendarDate = {">=$(=vSevenDaysAgo)"}, Status = {'DELIVERED'}>} OrderNumber)

[anseglko]

Okay! Thank you for the info

[mrainey18]

This did not work either.  It might be because we are on a different version of Qlik Sense.  What version are you running?  I'm going to download the latest version onto my desktop and see if I can get this to work.  Otherwise I'll do some fun stuff on the load to get it to work.

[anseglko]

I'm running April 2019