topic Re: Should I be using Aggr? in QlikView

Should I be using Aggr?

Wed, 24 Sep 2014 12:46:53 GMT

Hi All,

I am new to Qlikview and currently working on a project where I take the sum of a field for a number of individual loans and depending on the sum, I multiply that amount by a certain percentage to be placed in another field.

For example, IF(Aggr(SUM( loan), employee) < 1000, SUM(loan*.0035). This syntax is giving me the intended results but in the field in which I show the results, I am only getting the total and the highest loan for each employee. I want to be able to see the results from each loan not just the highest loan and the total.

I assume this is a result of using AGGR. Is there any other way to get the same output but show all the data I need?

Using COUNT(IF(Aggr(SUM( loan), employee) < 1000, SUM(loan*.0035)) is multiplying individual loans by .0035 and not the total sum of the loan field and outputting incorrect data.

Re: Should I be using Aggr?

Gysbert_Wassenaar — Wed, 24 Sep 2014 13:07:51 GMT

Can you post an example document with some data? Read this document if you're worried about confidential information: Preparing examples for Upload - Reduction and Data Scrambling

Re: Should I be using Aggr?

marcus_sommer — Wed, 24 Sep 2014 13:27:53 GMT

Maybe this is helpful: http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-used

- Marcus

Re: Should I be using Aggr?

Wed, 24 Sep 2014 14:09:11 GMT

I have posted the document I am working on. The goal here is to take the individual loan sum in [Loan Amount] and using this syntax

if(

aggr(sum(loan_amt),LoanOfficer) < 500000, sum(loan_amt*.0035),

if(

aggr(sum(loan_amt),LoanOfficer) > 500000 and aggr(sum(loan_amt),LoanOfficer) <= 700000, sum(loan_amt*.0050)

to get the data in [Tier_On_Commission]. This code works just fine for outputting my expected data. The issue here is that I want to see the sum for the individual loans as well NOT just the highest sum loan which you can see in the document. Data which are not the highest value are coming up as '-' ; yet the data is available if I were to drill down into the individual loan.The question is how do I get that data to show up on this sheet?

I assumed it was my use of AGGR() which is filtering the data to just show the highest sum.

Thanks for your help.

Re: Should I be using Aggr?

Wed, 24 Sep 2014 14:12:57 GMT

I have tried without using AGGR() but I do not get my intended results or the expression just does not work. Maybe I need to reword my logic?

Thanks.

Re: Should I be using Aggr?

Gysbert_Wassenaar — Wed, 24 Sep 2014 14:17:01 GMT

I'm afraid I cannot find the document you say you posted. Or are you referring to the screenshot? I cannot tell from a screenshot how to help you solve your problem.

Re: Should I be using Aggr?

Wed, 24 Sep 2014 14:38:58 GMT

I apologize for the confusion. How would I go about uploading the document?

Re: Should I be using Aggr?

Wed, 24 Sep 2014 16:57:21 GMT

Using AGGR() was unnecessary, and was the reason for the apparent null data I was getting. SUM(DISTANT<>) gave me the result I intended.