topic Re: Set Analysis with Variables in Visualization and Usability

Set Analysis with Variables

mrainey18 — Wed, 22 Dec 2021 18:28:09 GMT

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.

Re: Set Analysis with Variables

bharathadde — Thu, 22 Aug 2019 19:14:47 GMT

Try this

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

Re: Set Analysis with Variables

mrainey18 — Wed, 28 Aug 2019 16:33:31 GMT

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

Re: Set Analysis with Variables

bharathadde — Wed, 28 Aug 2019 16:45:13 GMT

Can you share sample data or .qvw file?

Re: Set Analysis with Variables

mrainey18 — Wed, 28 Aug 2019 16:47:15 GMT

No, unfortunately I can not.

Re: Set Analysis with Variables

anseglko — Thu, 29 Aug 2019 13:59:27 GMT

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

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

Re: Set Analysis with Variables

tresB — Thu, 29 Aug 2019 08:01:59 GMT

@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

Re: Set Analysis with Variables

anseglko — Thu, 29 Aug 2019 08:07:50 GMT

Okay! Thank you for the info

Re: Set Analysis with Variables

mrainey18 — Thu, 29 Aug 2019 13:08:59 GMT

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.

Re: Set Analysis with Variables

anseglko — Thu, 29 Aug 2019 13:23:17 GMT

I'm running April 2019

Re: Set Analysis with Variables

mrainey18 — Thu, 29 Aug 2019 13:34:12 GMT

This worked in the Desktop Version I just downloaded. Thank you. I'll have to figure out something for our current version of Qlik Sense.