Set Analysis vs if()

[#1]

I have been using set analysis to limit my select statements, as seen below:

sum({<Age_Bucket={1}>} Patient_Count)/sum(Patient_Count) => % of Population that is in Age Bucket 1

This works great until you make a selection for Age_Bucket, which then does not affect the numerator but changes the denominator. Which is some cases gives you numbers like 180% or 200%+

If I change my expressions to sum(if(Age_Bucket=1,1,0)) it works as I would expect.

I'm wondering if this is a viable option when dealing with a large data set. I'm worried that using the if statements will be detrimental to my performance.

Is there a better way to accomplish what I'm trying to do and still accommodate for a selection in the field used within the set analysis?

[swuehl · Accepted solution]

Jeremy,

I am not 100% sure what you want to achieve:

If you want to calculate the proportion of Age Bucket count compared to the total patient count, you can use something like

=sum({<Age_Bucket = {1}>} Patient_Count) / sum({1} Patient_Count)

If you want to regard your selection in Age_Bucket, so you get zero when you select a bucket different from 1, then you can use set intersection operator *:

=sum({<Age_Bucket *= {1}>} Patient_Count) / sum( Patient_Count)

[hic]

A third alternative could be to use

     sum({$<Age_Bucket={1}>} Patient_Count)/sum({$<Age_Bucket=>} Patient_Count)

which would make both the numerator and denominator independent of selections in Age_Bucket.

HIC

[#4]

I agree with Henric solution

Henric, why do you use $, is it just a habit or will it give some message to Qlikview to run faster?

I am assuming the default is $

sum({<Age_Bucket={1}>} Patient_Count)/sum({<Age_Bucket=>} Patient_Count)

[hic]

It is just a habit. It will not make any changes to how QlikView evaluates the formula.

I am a firm believer in writing code that is clear, distinct and easily legible. I dislike shortcuts and sloppy code. When you write code, you must always do that little extra that makes your code understandable for another developer. Omitting the $ sign is a shortcut that in some cases makes the expression less clear, so I think you should have it there. Maybe I am picky...

HIC

[swuehl]

You just need to be aware that there is a difference between

sum({<Age_Bucket={1}>} Patient_Count)/sum({<Age_Bucket=>} Patient_Count)

and

sum({$<Age_Bucket={1}>} Patient_Count)/sum({$<Age_Bucket=>} Patient_Count)

when you use these expressions in a chart that is set to an alternate state.

[#7]

Thank you! makes sense.

[#8]

Swuehl,  did you mean if I don't use the $, it uses the state of the object?