Weird Pick(Match()) behaviour with measures

[MartinW1]

I'm getting some really weird behaviour using Pick(Match()) with some measures, and feel like I've tried everything.

I have a table in Qlik Sense which I am using to show various metrics and compare to either a target or a comparison period. The comparison columns are being handled with measures that switch depending on what the user selects. Examples of the measures I'm using are:

[Qty] = Sum([Sales.Qty])
[QtySwitch] = Pick(Match([Comparator], 'vs Target', 'vs Comp'), [QtyTarget], [QtyComp])
[QtyComp] = Sum({<[Calendar.Period] =, [Calendar.SalesDate.autoCalendar.Date] = {">=$(vCompStartDate)<=$(vCompEndDate)"}>} [Sales.Qty])

Now the weird behaviour. When 'vs Comp' is selected, where there is no [Qty] on a particular line (ie there are no sales on the currently selected period), [QtySwitch] is returning null. However, the line does show (just with null values). Also, lines with a value for [Qty] are fine, and the total is correct (so the lines don't add up to the total). And the really weird bit is that if I put [QtyComp] in a column on it's own, it works perfectly. Also tried changing the Pick(Match()) to nested ifs and that doesn't work either (same behaviour).

Have I missed something obvious? Is this just a bug? Any suggestions for getting round the issue?

[rwunderlich]

You can get strange results if you don't use an aggregation function. In your case, I would use something like:

[QtySwitch] = Pick(Match([Comparator], 'vs Target', 'vs Comp'), Sum([QtyTarget]), Sum([QtyComp]))

-Rob

[MartinW1]

This leaves me with nested aggregation that it doesn't like.

[MartinW1]

Incidentally, I just tried 

[QtySwitch] = Pick(Match([Comparator], 'vs Target', 'vs Comp'), [QtyTarget], Sum({<[Calendar.Period] =, [Calendar.SalesDate.autoCalendar.Date] = {">=$(vCompStartDate)<=$(vCompEndDate)"}>} [Sales.Qty]))

And that also doesn't work, with the same behaviour. So this is seems to be unrelated to the separate measures?

[marcus_sommer]

A nesting of aggregations is possible if they are wrapped within an aggr(), which may look in your case like:

sum(aggr([QtyTarget], Dim1, Dim2))

whereby the Dim# are just placeholder for the needed dimension-context.

Further Qlik evaluates all branches of a conditional-calculation which makes it more likely that the result is NULL if any of the parts contains an invalid call.

There are some ways to catch NULL within the UI but they are depending on the data-model/data-set and the object-dimensions if and which way are possible - and it caused often more or less side-effects to the complexity, usability and performance. Therefore it's often better to populate the missing data within the data-model.

[Nagaraju_KCS]

use Aggr function

SUM(AGGR(Sum(QtyTarget), qty))

[MartinW1]

Just to update this, nothing suggested has worked; it seems to be a bug with conditional calculations. Even hard coding the sums into the Pick(Match()) (so no extra measures or date variables) and ensuring that all options evaluate to non null results doesn't work. I'd be curious to know if anyone else can replicate the issue. But for now, I have no solution.

[marcus_sommer]

I doubt that there is a bug within the conditional evaluation else that the data-set respectively the relationship between the data is the cause and that there is probably nothing wrong else it's been working as expected.

Key in your case is very likely the understanding of: NULL handling in QlikView - Page 3 - Qlik Community - 1484472.

[MartinW1]

Then can you explain why this works:

Sum({<[Calendar.Period] = >} [Sales.Qty])

But this doesn't:

Pick(Match([Comparator], 'vs Comp'), Sum({<[Calendar.Period] = >} [Sales.Qty]))

I don't see how that can be a model issue or null handling, or in fact anything other than a bug?

[marcus_sommer]

I assume that this: Match([Comparator], 'vs Comp') doesn't returned 1 else 0 or NULL. This means that either Comparator doesn't contained a value of: 'vs Comp' or the call of Comparator results in NULL because there is more as single value available or the relationship of the field in regard to the object and/or the selections prevent the return of any value.

Just put Match([Comparator], 'vs Comp') and [Comparator] as expressions in the chart.