# Qlik Sense App Development

Valued Contributor

## A Lot of AGGR Functions in a Quartile Chart

I have this quartile chart, but only the first one shows up. I've tested this method with other functions, but I think the expression is too long or holding too much data.

Here's my dimension:

Here's my expression (without Q3 and Q4 filled in):

Can anyone help see a problem or suggest ways I can get this to work?

Tags (5)
1 Solution

Accepted Solutions
MVP

## Re: A Lot of AGGR Functions in a Quartile Chart

It some kind of grain mismatch issue which is basically addressed by NODISTINCT. To read about grain mismatch look at HIC's blog here: Pitfalls of the Aggr function

24 Replies
MVP

## Re: A Lot of AGGR Functions in a Quartile Chart

Would you be able to share a sample may be to test out other options?

Valued Contributor

## Re: A Lot of AGGR Functions in a Quartile Chart

I cannot share any of the source data, but I can say that there are about 100 records in the [Account Executive] field, and about 4,000 records in the Loan_Amount field.

MVP

## Re: A Lot of AGGR Functions in a Quartile Chart

Please post your expression as text rather than a (rather small) image,

Valued Contributor

## Re: A Lot of AGGR Functions in a Quartile Chart

IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q1', SUM(AGGR(IF(RANK(AGGR(SUM(Loan_Amount),[Account Executive]))<COUNT(TOTAL DISTINCT [Account Executive])/4,SUM(Loan_Amount)),[Account Executive])),

IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q2', SUM(AGGR(IF(RANK(AGGR(SUM(Loan_Amount),[Account Executive]))>COUNT(TOTAL DISTINCT [Account Executive])/4,SUM(Loan_Amount)),[Account Executive]))))

Valued Contributor

## Re: A Lot of AGGR Functions in a Quartile Chart

It's just looking like I can't do multiple AGGR functions on the same fields within the same expression. Could this be why only the first one is working?

MVP

## Re: A Lot of AGGR Functions in a Quartile Chart

Have you tried adding NODISTINCT with your Aggr() functions? Aggr() seems to have issue with synthetic dimensions.

If(ValueList('Q1','Q2','Q3','Q4') = 'Q1',

Sum(Aggr(NODISTINCT If(Rank(Aggr(NODISTINCT Sum(Loan_Amount), [Account Executive]))<Count(TOTAL DISTINCT [Account Executive])/4,Sum(Loan_Amount)),[Account Executive])),

If(ValueList('Q1','Q2','Q3','Q4') = 'Q2',

Sum(Aggr(NODISTINCT If(Rank(Aggr(NODISTINCT Sum(Loan_Amount),[Account Executive]))>Count(TOTAL DISTINCT [Account Executive])/4,Sum(Loan_Amount)),[Account Executive]))))

MVP

## Re: A Lot of AGGR Functions in a Quartile Chart

I dont really understand your expression - there seems to be no difference between the two legs.

Having said that, you have another problem in that the Aggr() dimensions need to include all the chart dimensions in addition to the dimensions required for the expression. If you don't you will only see limited data (like the one bar you are seeing). Aggr() dimensions can only be fields, not calculated dimensions, so that rules out using purely synthetic dimensions like ValueList as a chart dimension with Aggr() expressions in the chart.

MVP

## Re: A Lot of AGGR Functions in a Quartile Chart

You will need to add a Quarter field to your calendar so you can use a field for the dimension rather than the ValueList.

Valued Contributor

## Re: A Lot of AGGR Functions in a Quartile Chart

Heyyyy that seems to have worked! Are you able to explain exactly what NODISTINCT is doing in this situation?

The chart shows the expression as stalwar1 has it, but I have also reworked my Quartile Calculations so they use set analysis and only one AGGR function.

SUM({<[Account Executive]={"=RANK(AGGR(NODISTINCT SUM(Loan_Amount),[Account Executive]),4)>COUNT(TOTAL DISTINCT [Account Executive])*.75"}>} Loan_Amount))