Using the = sign with variables in Set Analysis
by Ferran Garcia Pagans, Neeraj Kharpate, Philip Hand, James Richardson, Dr. Henric
Qlik Sense: Advanced Data Visualization for Your Organization
- Qlik Sense: Advanced Data Visualization for Your Organization
- Table of Contents
- Qlik Sense: Advanced Data Visualization for Your Organization
- Credits
- Preface
- 1. Module 1
- 1. Qlik Sense® and Data Discovery
- 2. Overview of a Qlik Sense® Application's Life Cycle
- 3. Empowering Next Generation Data Discovery Consumers
- 4. Contributing to Data Discovery
- 5. Authoring Engaging Applications
- 6. Building Qlik Sense® Data Models
- 7. Qlik Sense® Apps in the Cloud
- 8. Extending the Qlik® Analytic Platform
- 9. Administering Qlik Sense®
- 10. Sales Discovery
- 11. Human Resource Discovery
- 12. Travel Expense Discovery
- 13. Demographic Data Discovery
- 2. Module 2
- 1. Getting Started with the Data
- Introduction
- Extracting data from databases and data files
- Extracting data from Web Files
- Activating the Legacy Mode in Qlik Sense® desktop
- Extracting data from custom databases
- Invoking help while in the data load editor or the expression editor
- Previewing data in the Data model viewer
- Creating a Master Library from the Data model viewer
- Using a Master Library in the Edit mode
- 2. Visualizations
- Introduction
- Creating Snapshots
- Creating and adding content to a story
- Adding embedded sheets to the story
- Highlighting the performance measure in a bar chart
- Associating persistent colors to field values
- Using the ColorMix1 function
- Composition
- Relationships
- Comparison
- Distribution
- Structuring visualizations
- 3. Scripting
- Introduction
- Structuring the script
- Efficiently debugging the script
- Packaging the code in script files
- How to use sub routines in Qlik Sense®
- Optimizing the UI calculation speed
- Optimizing the reload time of the application
- Using a For Each loop to load data from multiple files
- Using the Concat function to store multiple field values in a single cell
- 4. Managing Apps and User Interface
- Introduction
- Publishing a Qlik Sense® application created in Qlik Sense® desktop
- Creating private, approved, and community sheets
- Publishing a Qlik Sense® application to Qlik Sense® cloud
- Creating geo maps in Qlik Sense®
- Reference lines in Sales versus Target gauge chart
- Effectively using the KPI object in Qlik Sense®
- Creating Tree Maps
- Creating dimensionless bar charts in Qlik Sense®
- Adding Reference Lines to trendline charts
- Creating text and images
- Applying limitations to charts
- Adding thumbnails – a clear environment
- Navigating many data points in a scatter chart
- 5. Useful Functions
- Introduction
- Using an extended interval match to handle Slowly Changing Dimensions
- Using the Previous() function to identify the latest record for a dimensional value
- Using the NetworkDays() function to calculate the working days in a calendar month
- Using the Concat() function to display a string of field values as a dimension
- Using the Minstring() function to calculate the age of the oldest case in a queue
- Using the Rangesum() function to plot cumulative figures in trendline charts
- Using the Fractile() function to generate quartiles
- Using the FirstSortedValue() function to identify the median in a quartile range
- Using the Declare and Derive functions to generate Calendar fields
- Setting up a moving annual total figure
- Using the For Each loop to extract files from a folder
- Using the Peek() function to create a currency Exchange Rate Calendar
- Using the Peek() function to create a Trial Balance sheet
- 6. Set Analysis
- Introduction
- Cracking the syntax for Set Analysis
- Using flags in Set Analysis
- Using the = sign with variables in Set Analysis
- Point in time using Set Analysis
- Using comparison sets in Set Analysis
- Using embedded functions in Set Analysis
- Creating a multi-measure expression in Set Analysis
- Using search strings inside a set modifier
- Capturing a list of field values using a concat() function in Set Analysis
- Using the element functions P() and E() in Set Analysis
- 7. Extensions in Qlik Sense®
- 8. What's New in Version 2.1.1?
- Introduction
- Using the visual exploration capability in Qlik Sense® 2.1.1
- Defining variables in Qlik Sense®
- Exporting stories to MS PowerPoint
- Using the Qlik Dev Hub in Qlik Sense® 2.1.1
- Using Extension editor in Qlik Dev Hub
- Using Qlik Dev Hub to generate mashups
- Embedding Qlik Sense® application on a website using a single configurator
- Using the Qlik DataMarket
- Creating dynamic charts in Qlik Sense®
- Using Smart Search
- Using smart data load profiling
- Conclusion
- A. Appendix
- 1. Getting Started with the Data
- 3. Module 3
- 1. Getting Ready with Predictive Analytics
- 2. Preparing Your Data
- 3. Exploring and Understanding Your Data
- 4. Creating Your First Qlik Sense Application
- 5. Clustering and Other Unsupervised Learning Methods
- 6. Decision Trees and Other Supervised Learning Methods
- 7. Model Evaluation
- 8. Visualizations, Data Applications, Dashboards, and Data Storytelling
- 9. Developing a Complete Application
- A. Bibliography
- Index
We can make use of variables and calculations in the set modifiers. The following recipe explains the syntax to use variables for comparison in sets and how to effectively use the = sign in the dollar sign expansions.
For the purpose of this recipe,we will be using an inline data load which contain shipment details for each customer. Load the following script in the Qlik Sense data load editor. Make sure that the last record in this script has the Month set to today's month and the DeliveryDate set to today's date:
Let vToday=Today (); Sales: LOAD DATE(Date#(DeliveryDate,'DD/MM/YYYY')) AS DeliveryDate, DATE(Date#(ShipmentDate,'DD/MM/YYYY')) AS ShipmentDate, Customer,Month,Volume,Sales,Supplier INLINE [ Customer,Month,DeliveryDate,ShipmentDate,Volume,Sales,Supplier ABC,Jan,01/01/2015,29/12/2014,100,10000,DEF ABC,Feb,02/02/2015,25/01/2015,100,10000,DEF ABC,Mar,03/03/2015,02/03/2015,400,12000,DEF ABC,Apr,04/04/2015,24/01/2015,100,10000,GHI DEF,Feb,03/02/2015,03/02/2015,200,25000,GHI DEF,Mar,25/03/2015,21/03/2015,300,25000,GHI DEF,Apr,18/04/2015,14/04/2015,200,25000,ABC GHI,Jan,24/01/2015,18/01/2015,200,8500,ABC GHI,Mar,14/03/2015,09/03/2015,200,7000,ABC GHI,Jun,06/08/2015,07/06/2015,200,5000,ABC ];
- Drag across a Table object from the Assets panel onto the sheet.
- Add Customer as dimension.
- Now add the following calculation as the measure and label it
Sales:Sum({$<DeliveryDate={'$(vToday)'}>}Sales) - Click on Save and then
. - The resultant table is similar to the following figure with only one record for customer GHI:
- Next, update the
Salescalculation as shown:Sum({$<DeliveryDate={'$(TODAY())'}>}Sales) - When you save this calculation, Qlik Sense won't be able to interpret the result and we will get the following output:
- Tweak the sales calculation by adding a = sign in front of
TODAY():Sum({$<DeliveryDate={'$(=TODAY())'}>}Sales) - The result will be as seen earlier with one record for the customer GHI.
We have defined the vToday variable in the script. This variable stores the values for today's date. When we use this variable inside the set modifier, we just use a simple $ sign expansion.
The vToday variable is calculated before the script is executed. However, Qlik Sense fails to interpret the result when we use the TODAY()function inside the set modifier instead of vToday. The reason being that the $ sign expansion needs to perform a calculation in the form of TODAY() and without the preceding = sign the date for today won't be calculated.
Hence, we proceed to TODAY() with the = sign. Once the = sign is in place, the sales for customers with today's delivery date are calculated.
If we are not using any calculation inside the set modifier then the variable can be defined with or without the = sign.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.