Greater Than, Less Than and Dates in Qlik Set Analysis

Greater Than, Less Than, and Dates in Qlik Set Analysis

When referencing dates, often we want to evaluate what happened between one date and another. Meaning… What actually occurred when the date was Greater than one point but less than another. Let’s dive into what each of these mean: Greater Than, Less Than, and Dates in Qlik Set Analysis

But how do we express GT and LT in Set Analysis?

“I’ve tried <= and >= that works in expressions, but then using them in Set Analysis expressions seems to break? What have I done wrong?” .. something you may ask yourself.

Understanding the placement of the identifiers between brackets is what you need to understand. An additional set of curly brackets inside the Set Analysis is required:

Sum({<Year = {">=2020"}> } Sales) 

: The Sum of Sales since the beginning of 2020.

S et the >= and <= inside the second set of curly brackets.

Other forms and examples:

{< year = {"<2021”} >} :

All years under 2012

{< year = {">=2019 <=2021"} >} :

The years between 2019 to 2021

{< hour = {8, 9, 10} + {">=12 <20"} >}

The hour 8, 9, 10 and the interval between hours 12 and 19

Further Extension on Date Fields:

Is there a way to find values greater than or less than a specific full date? Is the syntax the same?

For a date field we need to add a ($) indicator with the date field being evaluated against within it, in order to convert the evaluated field into a number:

Example:

Count ({<CreateDate= {"<=$(= [TodayDate])"}>} DISTINCT CustomerID)

All customers created up to and including today or any predefined date.

Now that we know how to work around a single date, what about a date range?

Example:

Sum({$<[SalesDate] = {">=$(=Date(AddMonths(Max([SalesDate), -1), 'YYYY-MM-DD')) <=$(=Date(Max([SalesDate]), 'YYYY-MM-DD'))"}>} [Amount])

In the example, we use the AddMonths() function to create a date 1 month prior to the maximum date found. The sum of Sales in a period where the SalesDate is greater than the prior month and less than the max date.

Up Next: Learn Qlik Document Types