3 Replies Latest reply: May 18, 2012 4:44 PM by Chris Whitcomb

# Basic beginner question

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

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

• ###### Basic beginner question

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

• ###### Re: Basic beginner question

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