topic Re: Using boolean logic in set analysis in QlikView

Using boolean logic in set analysis

Fri, 05 Jun 2015 23:06:32 GMT

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

Re: Using boolean logic in set analysis

marcus_sommer — Sun, 07 Jun 2015 09:44:46 GMT

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

Re: Using boolean logic in set analysis

swuehl — Sun, 07 Jun 2015 15:41:12 GMT

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.

Re: Using boolean logic in set analysis

hic — Sun, 07 Jun 2015 17:09:05 GMT

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

Re: Using boolean logic in set analysis

Wed, 10 Jun 2015 07:39:54 GMT

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.

Re: Using boolean logic in set analysis

swuehl — Wed, 10 Jun 2015 07:58:17 GMT

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.

Re: Using boolean logic in set analysis

hariprasadqv — Wed, 10 Jun 2015 08:03:01 GMT

Hi,

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

Re: Using boolean logic in set analysis

hic — Wed, 10 Jun 2015 08:08:24 GMT

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.

Re: Using boolean logic in set analysis

Wed, 10 Jun 2015 17:54:41 GMT

What I found to be interesting is that I’ve never seen an instance of this syntax before, the “sum({} field)). The interesting part is that this: the latter one that I have seen many places produces erroneous results (very small error, but an error nonetheless) while the first one that I essentially just concocted myself produces accurate results. I don’t know enough about the “behind the syntax” processing to understand why that is; could you explain that?

Thanks,

Charlie

Re: Using boolean logic in set analysis

plexpro52 — Wed, 01 Jul 2015 16:31:58 GMT

I'm trying to define a Dimension of CustomerName where the sum of sales<>0. Following the pattern of your example, I tried this:

{$<CustomerName = {"=Sum({$<InvoiceYear={$(vCurrentYear)}>}ItemSalesAmt)<>0"}>}

and when this produced an invalid dimension, I tried a simplified version, which I believe follows your template exactly:

{$<CustomerName = {"=Sum(ItemSalesAmt)<>0"}>}

Neither of these works. I have been using a CustomerName dimension which works, as well as a Measure like the embedded Sum(), without problem. Am I not permitted to define a dimension like this?

Re: Using boolean logic in set analysis

plexpro52 — Wed, 01 Jul 2015 16:49:22 GMT

Hi Charlie,

>> 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

I discovered this last week also, with some assistance from Michael Solomovich, and found that it appears to work. Like yourself, I have seen no example like this in the Help, nor yet about how to use the results of functions which return Boolean values.

I would like to see the explained, and more examples of actual usage in the Help.

Re: Using boolean logic in set analysis

Wed, 01 Jul 2015 17:23:41 GMT

I had no luck with the Boolean expression like you have either. That’s why I went with the format that you saw in the email you sent right after this one.

Re: Using boolean logic in set analysis

swuehl — Wed, 01 Jul 2015 17:47:28 GMT

René Valencourt wrote:

I'm trying to define a Dimension of CustomerName where the sum of sales<>0. Following the pattern of your example, I tried this:

{$<CustomerName = {"=Sum({$<InvoiceYear={$(vCurrentYear)}>}ItemSalesAmt)<>0"}>}

and when this produced an invalid dimension, I tried a simplified version, which I believe follows your template exactly:

{$<CustomerName = {"=Sum(ItemSalesAmt)<>0"}>}

Neither of these works. I have been using a CustomerName dimension which works, as well as a Measure like the embedded Sum(), without problem. Am I not permitted to define a dimension like this?

If you just put the term in curly brackets into the calculated dimension editor, this will not work.

The term only makes sense within an aggregation function, like sum(), count(), only().

It's typically used within an aggregation in an expression on expression tab, not (but with exeptions) within a calculated dimension.

Try using CustomerName as your dimension. Then you need to use a set expression like above within the aggregations you are using on expression tab. Let's assume you have a single expression

=Sum(ItemSalesAmt)

Then your expression with the set expression looks like

=Sum({$<CustomerName = {"=Sum({$<InvoiceYear={$(vCurrentYear)}>}ItemSalesAmt)<>0"}>} ItemSalesAmt)

This should now only show the Customers with an ItemSalesAmt different from zero for current IncoiceYear, but aggregating over all possible years.

Create an additional InvoiceYear list box. Select the current year. You should see the customers and their ItemSalesAmt as used for the set expression filter.

Now select a different year. You should see a subset of the same customers, but with their ItemSalesAmt for the selected year.

Re: Using boolean logic in set analysis

Anonymous — Thu, 19 Apr 2018 14:37:47 GMT

I think the lesson here (evident by the fact that it works in this qvw but not when I or a lot of other people try to do it) is that the fields to compare in the Boolean logic expression and the field to be aggregated all have to be in the same table. That doesn't work for the particular problem I'm trying to solve.

For those who are wondering why anyone would want to do this, you could take a look at this qvw, for one. It appears to be made in conjunction with the pdf referenced in the original post. For another example, here's what I'm trying to do:

I have some tables linked up by people. Sometimes those people are trained in one place and working in another place. I have a straight table displayed in my application with the dimension as the place where they're working on projects. I have various columns showing numbers regarding the projects they're working on.

I would also like to display how many people were trained at those locations in another column of the same chart. There are way more people trained people than there are working on projects, and there are many differences in fields between the two tables, so I don't want my data all in one table. There are also a lot more complications with the data model that I'm not going to get into here.

Normally, I would just link up the tables by place and then I could easily just display information from both tables side-by-side. But I need to have the finer-resolution link by person for other reasons such as making selections or displaying straight tables by person. I obviously can't link the tables by both person and place.

What I'm going to have to do is make a small table that joins the two other tables and aggregates the number of trained people by place, but linking it only to the place in the projects table. But it really seems like there should be a way to do this with set analysis, as evident in all of the posts about this kind of thing. Effectively, "If the other table has the same value in the trained place field as this first column has in the project place field, then count people from both tables."

Re: Using boolean logic in set analysis

marcus_sommer — Fri, 20 Apr 2018 06:34:05 GMT

I think this isn't mainly a question of set analysis and any boolean logics within or without it else a question how to design the datamodel to address your requirements. In your case it could be that you need to concatenate both tables or to create a link-table (I don't mean a table with aggregated data) to get a datamodel which suits your needs. Set analysis is a very powerful feature but it couldn't cure failures within the datamodel.

Beside this you need to ensure that your data(-quality) is sufficient to the views which should be provided - again any significantly lack within the raw-data couldn't be compensated with any measures afterwards.

Here a few links which could be helpful for you:

Concatenate vs Link Table

The As-Of Table

- Marcus

Re: Using boolean logic in set analysis

Anonymous — Mon, 23 Apr 2018 13:50:15 GMT

Hi Marcus,

It would be hard to overstate how many other functions these two tables (by project location and trained location) are already successfully accomplishing for the client, so I certainly wouldn't say there are failures in the data model. It's just that this new requirement requested by the client is not possible given the current data model and the fact that this idea of Boolean logic in set analysis doesn't work the way that people tend to think it will work.

I've got my solution by adding one small table to my data model. (So your phrase "lack within" the data model is more accurate, although not at all a significant lack. Again, I can't link up the tables by place because they're linked up with better, finer resolution [people] and I can't concatenate because my project data table would be full of null values, which is why I'm not using the Boolean logic in set analysis within one table as I mentioned before.)

The new table is linked up to the project data by location, but counts the number of people who were trained at that location. It only keeps places that are in the project dataset, since that's the only way it will be displayed in the application table. Like this:

Trained_Count_by_Project_Location:
Left Keep (Project_Data)
LOAD
Trained_Location as Project_Location,
Count(Person) as Trained_Count
Resident Trained_Data
Group By Trained_Location
;

I still think it would've been intuitive to do this in set analysis by programming the straight table to count the people trained at a location and display it in a column next to the location they work at as the dimension, i.e. something like Count({<-1 = "Trained_Location = Project Location">} Person), which quite a few people have seemed to have a need for.

Thank you for the links. I'll have to keep the As-Of Table in mind for future applications, especially more easily calculating rolling 12-month averages, which have seemed a little overcomplicated in the past.

Sincerely,

Brit

Re: Using boolean logic in set analysis

marcus_sommer — Mon, 23 Apr 2018 15:23:09 GMT

If I look on your dummy-expression I would say the logic could only work if the data-associations are set properly because the set analysis worked like a where-condition within the load of a table and couldn't create a new matching of the tables.

But I could imagine that you could get this with an aggr-function maybe something like this:

sum(aggr(-(Trained_Location = [Project Location]), Person))

whereby aggr-functions could consume a lot of performance and your keep-table might therefore be more suitable at all.

- Marcus