Using Set Modifiers based on another field {$}

Eddy — Sat, 16 Nov 2024 19:22:54 GMT

Hello,

in the Qlik help there is a statement that you can use a set modifier expression based on another field, like this:

sum({$<OrderDate = DeliveryDate>} Sales)

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Now, I constructed an example (QVF attached), that I want to do exactly that and it does not work:

Set Expression:

=sum({1<OrderDate=DeliveryDate>}SentAmount)

Fruits:
Load * Inline [
Fruit, State, SentAmount, SupplierID, BackAmount, OrderDate, DeliveryDate
Apple, Foul, 2, 1, 2, 201901, 201901
Apple, Good, 8, 2, 0, 201902, 201902
Pear, Foul, 5, 3, 5, 201903, 201903,
Pear, Good, 7, 3, 2, 201901, 201901,
Banana, Foul, 4, 4, 2, 201902, 201903
Banana, Good, 5, 5, 0, 201904, 201906
];

Of course I can use an if statement, in which case I get the desired result:

=sum(if(OrderDate=DeliveryDate,SentAmount))

But if Qlik help page states that sum({$<OrderDate = DeliveryDate>} Sales) is possible, why does it not work?

Which option is better in terms of performance with big data sets?

Kind Regards and thanks a lot,

Eddy

Re: Using Set Modifiers based on another field {$}

lorenzoconforti — Fri, 27 Dec 2019 15:25:10 GMT

For these type of comparisons the best option (and fastest) is to create an extra flag during load; you compare the two fields and then make your selections based on the new flag

The if statement is the second best option as it works on a row by row level of your data

The set expression is the least favourable option because it is evaluated once per chart and it's not (I believe) designed to make row by row comparisons; if you want to make it work you need to use something like this where you define two sets and you join them together (I'm not 100% sure myself why this works but it might be due to how data is grouped when the set analysis is perfomed):

{1 <OrderDate={"=DeliveryDate=OrderDate"}> + 1<DeliveryDate={"=OrderDate=DeliveryDate"}>}

topic Using Set Modifiers based on another field {$<FieldA = FieldB>} in App Development

Using Set Modifiers based on another field {$}

Re: Using Set Modifiers based on another field {$}