24 Replies Latest reply: May 19, 2016 11:27 AM by Oleg Troyansky

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

• ###### 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?

• ###### 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.

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

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

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

• ###### 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.

• ###### 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.

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

To clarify what my expression is trying to do: find the SUM of Loan_Amounts for Account Executives who are in the Upper 25%, 25-50%, 50-75%, and lower 25% (quartiles which has nothing to do with date).

In example I gave above, the only difference is the orientation of the >. The example was really finding the upper 25% of Account Executives and the lower 75%.

I will post the full expression and what the chart looks like shortly.

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

Do you really need the aggr() in the advanced search (which is already grouping by [Account Executive]?

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

should probably work as well

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

Yeah that actually works, too. I don't understand how this works behind the scenes, but it is giving me the correct data.

I do, however, rely on AGGR in other areas of my app which were also not working due to the issue that stalwar1 identified in which case I will have to do more testing of your proposed solution to see if they work in those cases as well.

Thanks!

• ###### 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?

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

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

• ###### 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

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

Here it is in all it's glory! The chart shows the SUM of Loan_Amounts for Account Executives who are ranked in the Upper 25% of all Account Executives, Upper Middle 25-50%, Lower Middle 50-75%, and Lower 25%.

The massive expression is as follows:

IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q1',SUM({<[Account Executive]={"=RANK(SUM(Loan_Amount),4)<\$(vQ1)"}>} Loan_Amount),

IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q2',SUM({<[Account Executive]={"=RANK(SUM(Loan_Amount),4)<\$(vQ2) AND RANK(SUM(Loan_Amount),4)>=\$(vQ1)"}>} Loan_Amount),

IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q3', SUM({<[Account Executive]={"=RANK(SUM(Loan_Amount),4)<\$(vQ3) AND RANK(SUM(Loan_Amount),4)>=\$(vQ2)"}>} Loan_Amount),

IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q4',SUM({<[Account Executive]={"=RANK(SUM(Loan_Amount),4)>=\$(vQ3)"}>} Loan_Amount)))))

The variables in this equation were used to keep the length of the expression manageable and I use them elsewhere in the App. They are taking the count of distinct Account Executives and dividing by 2 and 4 to get vQ2 and vQ1, and multiplying by .75 to get vQ3.

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

Hi everybody,

sorry I'm terribly late to the party, but allow me nevertheless explain some of the concepts mentioned here and perhaps offer a more elegant alternative:

1. AGGR and ValueList - so, the issue is not quite a "grain mismatch", as Sunny suggested, but rather related to the definition of a DISTINCT vs. NODISTINCT AGGR(). The DISTINCT AGGR, by definition, returns a single value for each distinct combination of the dimensions. This chart, because of the ValueList, technically requires multiple values - one for Q1, one for Q2, one for Q3 and one for Q4. The conditional logic that renders only one AGGR for each single value, is being essentially ignored - the AGGR still returns a single value, hence the initial problem of only having one bar. And, since AGGR() dimensions can only include fields (not functions), adding the VALUELIST is not an option. The solution can be achieved with NODISTINCT, however it's not always the correct calculation (depending on the expression).

2. The working solution can be, indeed, achieved without the use of AGGR(), as Stephan had suggested - because using Advanced Search in Set Analysis contains an implied AGGR() on the filtered Dimension (Account Executive in this case).

3. On the other hand, AGGR() could be used here in a lot more elegant way - replacing the ValueList and the 4 nested IF() functions in the Expression. The Calculated Dimension with the AGGR() function can calculate the Quartile per Account Executive, and the expression can be a simple sum(Amount). I'll try to formulate it here, without guaranteeing a 100% accurate syntax:

Calculated Dimension for Quartile:

= AGGR(

'Q' & ceil(RANK(SUM(Loan_Amount),4)/COUNT(DISTINCT TOTAL [Account Executive]) * 4)

, [Account Executive])

expression for Loan Amount:

SUM(Loan_Amount)

If you are interested in learning more about advanced uses of AGGR() and Set Analysis, please consider attending one of the upcoming sessions of the Masters Summit for Qlik - coming soon to Austin, TX and to Johannesburg, South Africa. You can also learn these and many other Qlik techniques from my recently published book QlikView Your Business.

cheers,

Oleg Troyansky

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

Well this is a lot smarter than what I was doing.

Thanks!

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

I'm going to write a blog post, describing a similar issue and the possible solutions

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

Cool, let me know if you'd like any additional input.

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

By the way, I just spent hours resolving an error with this equation. That is, the AGGR around the RANK equation will not work properly if you have any null values in the AGGR field (Account Executive).

Therefore, I had to add some set analysis and change the RANK mode (in case of ties) to make it perfect.

= AGGR({<[Account Executive]=P()>} 'Q' & CEIL(RANK(SUM(Loan_Amount),2)/COUNT(DISTINCT TOTAL [Account Executive]) * 4), [Account Executive])

Thanks!

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

Hi Tim!

this is a nice finding, about the NULL values, I wasn't aware of it. For the RANK mode, don't you want to use mode 4 - number any equal values sequentially?

Oleg Troyansky

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

The problem with the sequential numbers is that ties could be split between two quartiles. Using mode 2 takes the average rank of all those that tied and forces all ties to at least be in the same quartile, though it can make for some funky looking charts when there are ties with small groups with populations like 5 or 11.

Here's the information about the Rank function: https://help.qlik.com/en-US/sense/1.1/Subsystems/Hub/Content/ChartFunctions/RankingFunctions/rank.htm

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

Yes, exactly! Depending on your analytical needs, you may want to keep all the ties together or prefer to keep identical number of entries in all buckets. For bucket analysis, the need to keep identically sized buckets prevails, despite the ties.

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

Hi everyone!

For anyone who is still confused by this issue, I published a blog article on my web site:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies

Enjoy!

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Austin, TX and Johannesburg, South Africa!