Expression within a set analysis

[vidamakiling]

Hi, thank you for taking the time to read my question.

I have this data set below:

EmployeeID	Hour1	Hour2	Salary
1	-5	0	500
1	5	0	250
2	0	1	700
3	0	0	500
4	2	0	250
4	3	0	700
5	5	0	500
5	-2	-1	250
5	-3	1	500
6	2	0	250
6	-2	0	700

And what I'd like to happen is to display only that has a total of 0 for Hour1 and Hour2 and display the sum of the salary as well. Below is the desired result:

EmployeeID	Hour1	Hour2	Salary
1	0	0	750
3	0	0	500
5	0	0	750
6	0	0	950

Here's what I tried to use:

Sum({<Hour1 = {"=sum(Hour1)=0"},Hour2={"=sum(Hour2)=0"}  >}Salary)

But obviously, it is not working. Appreciate your help!

Thanks in advance! 

[sunny_talwar]

From what I have heard and experienced set analysis does take some toll on your resources if it is a humongous dashboard. When I say Humongous, I am talking about 1 billion+ rows where I have seen even a simple set analysis like this Sum({<Flag = {1}>} Sales) vs Sum(SalesFlag1) can differ in performance.

But anything smaller the performance difference is almost negligible. In those cases, it almost never make sense to create an additional field (especially by pre aggregating them in the script) would make it unnecessary and should be avoided. In addition there is another problem with pre aggregating in the script, let's say we have data like this

Region, SalesRep, Hours, Salary
US, A, 0, 740
UK, A, 20, 80

Now if we pre-aggregate this in the script we will see that Sum(Hours) <> 0, but now this becomes static and your result can't be changed based on selection, where as a set analysis based selection will show Sum(Salary) if Region  = 'US' is selected. You may not want this to happen, but just saying that you lose the flexibility.

In the end, I understand and respects everyone comments here, but saying

It is a good practice to avoid Set Analysis as much as possible for two reasons:

1. It is heavy to process
2. Sometimes can be complex to create, maintain and even understand

 the below might be a little mis leading as it may push a lot of the new users away from set analysis. Having used QlikView for almost 5 years not, I think set analysis is one of my best friends when it comes to QlikView.

I would love to hear other people views on this as well. @marcus_sommer, @tresesco, @hic, @rwunderlich

[marksouzacosta]

I think you are missing my point here.

Set Analysis is great and definitely have its place but it think it should not be used every time, specially when it can be easily simplified or even replaced in the Load Script - the problem in this post is a good example for that.

In the other hand, your data sample for instance is a great example where Set Analysis is a way better tool to be used than pre-calculating every possible aggregation in the Load Script - that will be actually crazy 🙂

[marcus_sommer]

I totally agree with Sunny that the suggestion of avoiding set analysis is quite misleading in the matter of creating applications. Of course it has some kind of overhead against a pure calculation like: sum(Field) and it has also a slightly more complex logic/syntax as the classical if-then-else approach (but nothing what couldn't be learned by a few hours and attempts especially by using the great blog-postings from HIC to this topic here in the community).

But I think the real question is what could be sensible alternatives? Of course there are most often various ways possible to provide the needed measures and views like: splitting it into different applications, aggregating and/or pre-calculating it within the script, flag it, doubling data-structures, using the above mentioned if-logics, ...

Will any (combination) of these alternatives have a general benefit against set analysis - I really doubt it. My personal suggestion is to start the development of an application with the attempt to create a datamodel in the direction of a star- or snowflake-scheme without any aggregating and/or pre-calculating and then really using set analysis (if it's needed) in the UI.

This keeps the logics simple and usually the performance is fast enough - and it's for the most users and scenarios the best compromise in regard of the needed efforts and their results. Even if this approach isn't sufficient in the end I suggest to start with it because it will be a good prototype to validate the data and the used logics - and any kind of further optimization could come on top.

- Marcus

[Dan-Kenobi]

If you HAVE to make something slowe (or if you need to add complex expressions), ALWAYS choose the script over the UI. You can wait for the script to run, but users can't and won't wait for clicks on screen. Choosing slower UI will only generste more support tickets to your support team.
Of course, if the dataset is small enough and you'll have good performance either way... then go with the script again. It creates good performance habits, less UI complexities and better data modeling skills (which can then be leveraged by other apps consuming it later).

[sunny_talwar]

@Dan-Kenobi and @marksouzacosta

I guess we are all saying the same thing and nobody disagrees with the fact that a script based solution (if and when possible) will out perform a UI based solution (99 out of 100 times, there are scenarios when it is not true, but let's not go there to make matters worse here). But what I am trying to say is that stating this may not be right thing for a new user to read

"good practice to avoid Set Analysis"

As a new user, when I read this, I am thinking set analysis is a bad thing and I need to look at alternatives. When indeed it isn't that bad.

I respect everyone's opinion and hope my comments doesn't hurt anyone's feelings.

Best,
Sunny

[marcus_sommer]

I could not agree. Because it's not mandatory that the UI is the biggest bottleneck within your environment especially if you have multiple and/or quite small time-frames for refreshing the data. Further not each calculation in the UI needs to be instantly finished - we may argue what instantly could mean - because it's not essential for each scenario and/or user. Not a few users come from other tools like SAP BW / BO and they will be very happy if they just need to wait a few seconds.

Further by really pre-calculating everything - how to handle dozens or maybe even hundreds of such aggregation-tables? Of course it's not impossible but that this should be ALWAYS preferable - I don't think so.

- Marcus

[Dan-Kenobi]

I could agree with you, but then we'd both be wrong.
Joking 🙂

But, seriously now, there are general rules, and exceptions. And exceptions should be treated as such.
You're, of course, free not to concur. I'm just outlining what has been consistently working for me in the past 7 years of Server / Script / UI optimization as a general rule and best practice.
+UI problems = +support tickets

Not to say Set Analysis are bad, at all. They are useful, powerful and should be used. With care...

[marksouzacosta]

Nobody is saying that. I'm just saying that Set Analysis is not the answer for every question.
You are bringing scenarios that is out of the scope of this topic and in this topic its clear that changing the Load Script is a better option. 

Sounds that you are trying to convince us that in the case of this topic it is better to have many users calculating over and over the expression below

Sum({<EmployeeID = {"=sum(Hour1)=0 "}>*<EmployeeID = {"=sum(Hour2)=0 "}>} Salary)

Than the one below, that you just calculate once, without losing any data granularity:

Sum(TotalSalary)

This is not about Data Model vs Set Analysis war. It is about what is more appropriated to solve a problem and to be able to judge that we have to learn both ways.

[vidamakiling]

Thank you all! This discussion has been very interesting and very helpful! Looking forward to more of this with you all. Thank you so much!

[sunny_talwar]

Yet again, I think you are missing the point that the assumption is that this is part of a bigger convoluted dashboard where this might not be easy to be solved in the script. If the two conditions meet below then I am not against this doing in the script

1) Only few expressions used in the dashboard (we won't need to create 100 of fields for 100 of expressions) - Would you rather create 100 new fields? If you do, then do you think that will improve the performance of your dashboard? It might help you improve on one end, but by increasing the size of the dashboard which hinder the performance.

2) The result doesn't change based on the selections

But if the above simplifying assumptions are not true, then we hit a road block.

Having said all the above, I think you provided a solution by simplifying the problem to just this. Whereas we tried to assume that this could be part of something bigger. Do you agree? If you do then we can end this discussion here.