A Lot of AGGR Functions in a Quartile Chart

[TKendrick20]

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?

[sunny_talwar]

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

[TKendrick20]

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.

[jonathandienst]

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

[TKendrick20]

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]

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?

[sunny_talwar]

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

[jonathandienst]

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.

[jonathandienst]

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]

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