How to return a value if ANY of the dimension's values match a certain criteria?

[FakeJupiter]

Hello!

I am stuck on the following problem.

I have a dimension called Name. The same name can have several different dates attributed to it. I want to make a table/chart that shows the list of all names and a column next to it specifying whether ANY of the Name's dates are within a predefined period.

For instance, if "John" has 3 dates - 05.12.2019, 06.12.2019, and 12.12.2019, and the specified period criteria is of range 10.12.2019 - 31.12.2019, then I want to show "Yes" next to John's name.

I made a simple solution that does this, but for each date separately, repeating the names. I, however, need each name to be showed only once in the column if any of its dates match the date criteria.

Thank you in advance!
Ray

[tresesco]

You probably are using date as second dimension in your chart, if so - remove it. And try an expression something like:

Dim 1: Name

Exp : If(Min(DateField)>= <From date> and Max(DateField)<= <To date>, 'YES')

<From date> -  smaller date in your comparison range

<To date> -  bigger date in your comparison range

[FakeJupiter]

Thanks, it almost works.

The problem is that there can be a situation like this:

From date: 15.12.2019; To date: 20.12.2019
Date1: 05.12.2019
Date2: 16.12.2019
Date3: 22.12.2019

In this case it wouldn't show 'Yes'; although, Date2 would fall into the criteria range.

Also, shouldn't there be "Or" in the middle of the expression instead of "And". Because, if, given the same criteria range, where under one name would be dates Date1 and Date2, it wouldn't return "Yes".

[tresesco · Accepted solution]

So you are saying ANY date falling in the range ,just replacing and with or would not work there. Try like:

If( Min(Date>= fromDate  AND  Date<=toDate)= -1, 'YES')

[FakeJupiter]

Great! This works. Thank you!

I just don't fully understand how the expression works. How and why can this "Min(Date>= fromDate  AND  Date<=toDate)" return "-1"?

[tresesco]

Min() is used to get one output from multiple records (here individual comparisons). In Qlik, True is -1 and False is 0. Therefore, the condition within min() generates series of -1 and 0. If at least one True is there, min() would return -1.

Hope this helps. 

[FakeJupiter]

Thanks a bunch!