Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
Get Started Transforming Your Data in Snowflake - feature img

Greater Than, Less Than and Dates in Qlik Set Analysis

  • How-Tos FAQs
  • November 24, 2020

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.

Set 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

Transform Data in Snowflake

The only multi persona SaaS solution for data transformation in Snowflake

Learn more

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

More Resources We Think You Might Like

Qlik

GeoAnalytics and Google Maps Extensions for Qlik

Through the years… we’ve experienced many different options for Google Maps Extensions and ...

  • How-Tos FAQs
  • November 15, 2020
Qlik

Current Selections in a Qlik Chart Title

Current Selections in a Qlik Chart Title You have created a chart, and you want Current Selection...

  • How-Tos FAQs
  • November 24, 2020
Qlik

How Do I Get the Number of Months Between Two D...

Number of Months Between Two Dates in Qlik In Qlik Sense, there are no built-in functions that ca...

  • How-Tos FAQs
  • October 21, 2020