
Re: A Lot of AGGR Functions in a Quartile Chart
Sunny Talwar May 13, 2016 9:32 AM (in response to Tim Kendrick)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
Tim Kendrick May 13, 2016 9:40 AM (in response to Sunny Talwar )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
Jonathan Dienst May 13, 2016 9:42 AM (in response to Tim Kendrick)Please post your expression as text rather than a (rather small) image,

Re: A Lot of AGGR Functions in a Quartile Chart
Tim Kendrick May 13, 2016 9:47 AM (in response to Jonathan Dienst )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
Jonathan Dienst May 13, 2016 10:19 AM (in response to Tim Kendrick)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
Jonathan Dienst May 13, 2016 10:18 AM (in response to Jonathan Dienst )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
Tim Kendrick May 13, 2016 11:10 AM (in response to Jonathan Dienst )To clarify what my expression is trying to do: find the SUM of Loan_Amounts for Account Executives who are in the Upper 25%, 2550%, 5075%, 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
Stefan Wühl May 13, 2016 11:14 AM (in response to Tim Kendrick)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
Tim Kendrick May 13, 2016 11:21 AM (in response to Stefan Wühl )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
Tim Kendrick May 13, 2016 10:00 AM (in response to Tim Kendrick)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
Sunny Talwar May 13, 2016 10:04 AM (in response to Tim Kendrick)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
Tim Kendrick May 13, 2016 10:24 AM (in response to Sunny Talwar )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
Sunny Talwar May 13, 2016 10:26 AM (in response to Tim Kendrick)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
Sunny Talwar May 13, 2016 10:29 AM (in response to Tim Kendrick)




Re: A Lot of AGGR Functions in a Quartile Chart
Tim Kendrick May 13, 2016 11:25 AM (in response to Tim Kendrick)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 2550%, Lower Middle 5075%, 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
Oleg Troyansky May 13, 2016 12:35 PM (in response to Tim Kendrick)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
Tim Kendrick May 13, 2016 1:42 PM (in response to Oleg Troyansky )Well this is a lot smarter than what I was doing.
Thanks!

Re: A Lot of AGGR Functions in a Quartile Chart
Oleg Troyansky May 13, 2016 1:50 PM (in response to Tim Kendrick)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
Tim Kendrick May 13, 2016 2:14 PM (in response to Oleg Troyansky )Cool, let me know if you'd like any additional input.



Re: A Lot of AGGR Functions in a Quartile Chart
Tim Kendrick May 18, 2016 1:41 PM (in response to Oleg Troyansky )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
Oleg Troyansky May 18, 2016 3:39 PM (in response to Tim Kendrick)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?
thanks for adding your input!
Oleg Troyansky

Re: A Lot of AGGR Functions in a Quartile Chart
Tim Kendrick May 19, 2016 11:17 AM (in response to Oleg Troyansky )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/enUS/sense/1.1/Subsystems/Hub/Content/ChartFunctions/RankingFunctions/rank.htm

Re: A Lot of AGGR Functions in a Quartile Chart
Oleg Troyansky May 19, 2016 11:27 AM (in response to Tim Kendrick)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
Oleg Troyansky May 16, 2016 6:19 PM (in response to Tim Kendrick)Hi everyone!
For anyone who is still confused by this issue, I published a blog article on my web site:
QTip # 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!