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 ];
Sales
:Sum({$<DeliveryDate={'$(vToday)'}>}Sales)
Sales
calculation as shown:Sum({$<DeliveryDate={'$(TODAY())'}>}Sales)
TODAY()
:Sum({$<DeliveryDate={'$(=TODAY())'}>}Sales)
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.