Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

tkendrick20
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.

Quartile Chart.PNG

Here's my dimension:

Quartile Dimension.PNG

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

Quartile Expression.PNG

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

1 Solution

Accepted Solutions
MVP
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
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?

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

Re: A Lot of AGGR Functions in a Quartile Chart

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

tkendrick20
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]))))

tkendrick20
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
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
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
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.

tkendrick20
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?

Quartile Chart Working.PNG

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