topic Re: Set Analysis vs if() in QlikView

Set Analysis vs if()

Wed, 07 May 2014 19:54:51 GMT

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?

Re: Set Analysis vs if()

swuehl — Wed, 07 May 2014 20:05:37 GMT

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)

Re: Set Analysis vs if()

hic — Wed, 07 May 2014 20:44:13 GMT

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

Re: Set Analysis vs if()

Wed, 07 May 2014 21:25:14 GMT

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)

Re: Set Analysis vs if()

hic — Thu, 08 May 2014 08:43:43 GMT

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

Re: Set Analysis vs if()

swuehl — Thu, 08 May 2014 10:26:57 GMT

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.

Re: Set Analysis vs if()

Thu, 08 May 2014 12:53:28 GMT

Thank you! makes sense.

Re: Set Analysis vs if()

Thu, 08 May 2014 12:59:35 GMT

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