Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis vs if()

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

7 Replies
swuehl
MVP
MVP

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)

View solution in original post

Henric_Cronström

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

Not applicable
Author

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)

Henric_Cronström

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

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.



Not applicable
Author

Thank you! makes sense.

Not applicable
Author

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