topic Re: Basic beginner question in QlikView

Basic beginner question

Thu, 10 May 2012 21:15:20 GMT

I work in the financial services industry and have a table that looks like below (for illustration purposes):

Loan #, Interest Rate, Interest Rate Bucket, Balance

1, 1%, 0-1%, $57

2, 2%, 2-3%, $23

3, 3%, 2-3%, $93

4, 4%, 4-5%, $3456

5, 6%, 6-7%, $1134

6, 9%, Over 8%, $3874

In reality, the data set has millions of loans. Thus, in the load script, we bucket each loan's interest rate into a set of buckets that we call a 'Note Rate Dimension'. This allows me to make a basic chart where the Y-axis is the sum of the balances in each bucket and the dimension in the bucket. What I'm struggling with is that, like the sample data set above, our balance in our lower interest rate buckets are small. On the chart I'd like to eliminate them by doing the following: "If the sum of the balance in each bucket is less than x, then don't show that bucket as a dimension on the chart." I've looked at the blogs and documentation but I'm really at a loss being fairly new to this product.

Re: Basic beginner question

Wed, 16 May 2012 19:57:30 GMT

Hi,

Pls. check the attached qlikview file to see if it answers your question.

I set the treshold to 100 in the calculated dimension.

Best regards,

http://quickdevtips.blogspot.com

Re: Basic beginner question

rwunderlich — Thu, 17 May 2012 07:05:21 GMT

You should be able to code your expression as:

=if(sum(Balance) > 1000, sum(Balance), 0) // Use the sum if > 1000, else zero

and then utilize the default of "Suppress zero values" on the Presentation tab to eliminate those dimensions that calculate to zero.

-Rob

http://robwunderlich.com

Basic beginner question

Fri, 18 May 2012 20:44:17 GMT

Exactly what I needed. Thank you very much. It helped me understand the aggr function as well!