topic Re: A Lot of AGGR Functions in a Quartile Chart in App Development

A Lot of AGGR Functions in a Quartile Chart

TKendrick20 — Sat, 16 Nov 2024 10:38:31 GMT

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

sunny_talwar — Fri, 13 May 2016 13:32:25 GMT

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

TKendrick20 — Fri, 13 May 2016 13:40:06 GMT

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

jonathandienst — Fri, 13 May 2016 13:42:58 GMT

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

Re: A Lot of AGGR Functions in a Quartile Chart

TKendrick20 — Fri, 13 May 2016 13:47:57 GMT

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

TKendrick20 — Fri, 13 May 2016 14:00:49 GMT

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 — Fri, 13 May 2016 14:02:58 GMT

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

jonathandienst — Fri, 13 May 2016 14:17:31 GMT

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

jonathandienst — Fri, 13 May 2016 14:18:20 GMT

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

TKendrick20 — Fri, 13 May 2016 14:24:08 GMT

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 — Fri, 13 May 2016 14:26:58 GMT

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 — Fri, 13 May 2016 14:29:29 GMT

Also look here:

Using multiple variables in ValueList that are AGG() functions

Re: A Lot of AGGR Functions in a Quartile Chart

TKendrick20 — Fri, 13 May 2016 15:10:20 GMT

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

swuehl — Fri, 13 May 2016 15:14:24 GMT

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

TKendrick20 — Fri, 13 May 2016 15:17:20 GMT

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

TKendrick20 — Fri, 13 May 2016 15:21:57 GMT

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

Oleg_Troyansky — Fri, 13 May 2016 16:35:26 GMT

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

TKendrick20 — Fri, 13 May 2016 17:42:12 GMT

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

Thanks!

Re: A Lot of AGGR Functions in a Quartile Chart

Oleg_Troyansky — Fri, 13 May 2016 17:50:30 GMT

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

TKendrick20 — Fri, 13 May 2016 18:14:10 GMT

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