Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
FakeJupiter
Creator
Creator

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

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

Labels (3)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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')

View solution in original post

6 Replies
tresesco
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

Thanks a bunch!