Using boolean logic in set analysis - Qlik Community

Report Inappropriate Content · ‎2015-06-05

I'm having a very hard time understanding how Boolean logic works in set analysis. I have scoured the Qlik Community and found explanations that seem clear but then aren't born out upon application; even advice from people with Guru status. There is an example given in a very useful source "Les set analysis_ENG.pdf' that goes like this:

Syntax: {<Dimension = {"=boolean condition"}>}

Attention: the searched dimension cannot be also in the boolean condition. If needed, create an integer key with Autonumber().

We want to get the sales that have been delivered the same day. We have two fields : DayDelivery and DayOrder

Sum({<KeyAutoNumber = {"=(DayDelivery=DayOrder)" } >} Sales)

Since "Autonumber" generates integers the are assumed to be non-zero, this seems to imply that, from a Boolean point of view, "KeyAutoNumber", as generated by Autonumber, will always evaluate to True, since it will never be zero so that only True results of DayDelivery=DayOrder will be selected.

If that's the case, then why wouldn't this work: I set field called "negone" in the script to be a constant -1, which is the defined Boolean value of true in Qlikview, Then I use this: sum({<negone= {"(attending_doc=operating_doc)"}>} revenue), and even though I know there are cases where I know attending_doc=operating_doc, it always produces zero revenue. Can someone explain this? Thanks

marcus_sommer · ‎2015-06-07

It's not clear want do you want to do and where there are problems. The general logic from set analysis is:

sum({< Dimension = {CertainValues}>} value)

whereas CertainValues as a boolean condition to the Dimension worked which meant that only these values will be added where on dimension-level the condition returned a TRUE. Dimension shouldn't be an autonumber - it needed a "real" field. And of course the CertainValues could be an expression or a variable which is somehow calculated and which return a list of values. Important is that such calculation will be calculated globally for the chart and not on row-level.

That meant I think your expression should look more like:

sum({< DayDelivery = {"$(=max(DayOrder))"}>} value)

Further for handling several date-areas within an application see: Canonical Date

- Marcus

swuehl · ‎2015-06-07

Charlie,

I don't understand the 'Attention: ..' part of your posting or why this should be valid (what does 'cannot be in boolean condition' mean in that context?).

To my knowledge, there is no general limitation on the values of the dimension when using the set modifier mentioned above.

If you use a set expression like

{<Dimension {"=boolean condition"}>}

it's actually telling QV to create an internal table with dimension Dimension and Expression

=boolean condition

Every dimension value where the expression returns not zero (or TRUE, if you like) will be added to the list of values QV will use for a selection in field Dimension. Using the double quotes in the set modifier is also called 'using a search expression', you don't explicitely state the Dimension field values you want to select, but use an expression for filtering.

A common search expression / boolean condition would be something like

=sum(Sales) >1000

As you see, since QV creates an internal table, you can use aggregations over dimension Dimension in your search expression.

Coming back to your issue, what do you get when you create a straight table chart with dimension negone and expression:

=attending_doc=operating_doc

?

You probably know that using fields without aggregation in an expression will only return values when there is only one value possible (like adding an only() function around the field names).

Thus, when you have more than a single value for attending_doc and operating_doc related to each dimension value of negone, you won't get a result.

Maybe there are other (set) expressions that fulfill your requirement, but you need to describe your setting (data, model, requirement) a little more in detail if you want more detailed help.

hic · ‎2015-06-07

To chime in with Marcus & Stefan: You cannot use any Boolean expression. You have to use one that contains an aggregation, e.g:

{$<Customer = {"=Sum(Sales)>100"}>}

{$<Customer = {"=Count(distinct OrderID)=100"}>}

The first one will find the customers that have sales > 100 and the second one those that have exactly 100 orders. Note that a customer can have many values of Sales and many orders, and so the search implies an aggregation. Conceptually it is like a For-Next loop:

For each Customer

If Sum(Sales)>100 then include in outer calculation

Next Customer

HIC

Report Inappropriate Content · ‎2015-06-10

I found an interesting way of incorporating the boolean expression within the main aggregation, but outside of the set analysis. Here's what I was trying to do without success:

sum({<set expressions,boolean expression>} field) boolean expression: {"=(fld1=fld2)"}

Here's what works perfectly:

sum({<set expressions>} if(boolean expression,field)) boolean expresson: fld1=fld2

I've never seen this before and didn't realize it was possible, but it works well; instant response time with a 10+ million record doc.

swuehl · ‎2015-06-10

If the comparison needs to be done on a record base, you need to do it the second way.

But how does your first version looks like in example? Is still can't really get what you mean with 'boolean expression' in this context.

{"=(fld1=fld2)"} is syntax for an element list of a field selection, so you need to state a field name in combination:

field = {"=(fld1=fld2)"}

and with more complete set analysis syntax_

{<field = {"=(fld1=fld2)"} >}

As discussed before the boolean within the search will potentially not return an unambiguous answer when iterated over all record combinations related to field, thus defaulting to false.

hariprasadqv · ‎2015-06-10

Hi,

I got something from Community.Try this, it might help you.

hic · ‎2015-06-10

Yes, you can of course do this. Just be aware of the following potential problems:

The If()-function is evaluated on every record in your 10+ million record data set. This could imply a performance problem.
A normal aggregation, e.g. Sum( { set expression } Amount ), will sum over the records of the table where the field Amount is found. But your aggregation will sum over the records of a table which potentially could be larger: The product of Amount x fld1 x fld2; like a join between the three. If fld1 and fld2 are in the same table as Amount, you have no problem.

But if the response times are acceptable, and there are no duplication of records due to the 2nd bullet, there is no reason why you shouldn't use this solution.