Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using boolean logic in set analysis

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

16 Replies
plexpro52
Creator
Creator

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.

Not applicable
Author

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.

swuehl
MVP
MVP

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.

Anonymous
Not applicable
Author

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

marcus_sommer

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

Anonymous
Not applicable
Author

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

marcus_sommer

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