Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregation by Name

Hi and thank you in advance for any assistance!

I am currently attempting to create a chart to measure the number of different coaches that have coached advisors (grouped by sum totals) against the % of focuses that have improved (grouped by sum of total coaches and averaged by the count of each sum). I have been told that I need to create an aggregation for my "Num of Diff 1-2-1 Coaches" expression, so that I can set this as the dimension in my chart, but I've not used the Aggr function before and can't seem to figure it out.

I hope this makes sense!

The dimension is:

Non-Group Advisors

=IF([Prob Group]<>1 and [Est Group]<>1,[Advisor Name])

The expressions are currently thus:

Num of Diff 1-2-1 Coaches     

Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Coach)

% of Focuses Improved

Sum(Improved?)/Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Focus)

This produces this table (advisor names covered):

Table.JPG.jpg

1 Solution

Accepted Solutions
Not applicable
Author

Have now resolved final issue! (with help of a colleague)

Sum(Aggr((Sum (Improved?)/Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Focus)),[Advisor Name]))

/ count(aggr(Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Coach),[Advisor Name]))

Many thanks for your assistance 🙂

Capture2.JPG.jpg

View solution in original post

5 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Sorry, I don't understand it. Perhaps you can post a sample document with data and an example calculation for an advisor.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert,

The document is pretty complex with a number of different tables feeding into it, so it would be rather difficult for me to create sample data to match.

What I would like to have is the table pictured in the original post, but with the "Num of Diff 1-2-1 Coaches" as the dimension, rather than "Non-Group Advisors".

The "Num of Diff 1-2-1 Coaches" looks at the number of distinct coaches that have coached each distinct advisor - this is calculated by doing a distinct count of the coaches names on each row for each advisor, excluding certain focus fields and (established/probationary) group session flags.

The "% of Focuses Improved" expression takes the sum of the "Improved?" flag (if the advisor has improved, the column reads 1), then divides this by a distinct count of the foci used when coaching this advisor, e.g. if 4 different foci have been used when coaching the advisor and they have improved on 1 focus, the result will be 25%

The resulting table should look something like:

8          33.33%

7          66.67%

6          47.78%

5          60.00%

4          53.67%

3          80.67%

2          90.00%

1          10.00%

I hope this sheds a little light and I will see if it is possible to get some test data together at some point.

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

So you want to use a calculated dimension. Be aware that those can be very costly in terms of performance.

Try: aggr( Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Coach), [Advisor Name])


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks Gysbert 🙂


Managed to get to that bit myself (in the end) but now the tricky part is doing an average of the % improvements. I have been trying to do double aggregation to calculate the average of the % for the calculated dimension, but I'm stumped (as is my boss).

So, I need to now find the average % of focuses improved for each number that the aggregated calculated dimention holds, based on the total count of advisors that have had that number of coaches.

The closest I have gotten so far is this:

Sum (Improved?)/Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Focus))
/Count(aggr(Count({$<
[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Coach),[Advisor Name]))

Not applicable
Author

Have now resolved final issue! (with help of a colleague)

Sum(Aggr((Sum (Improved?)/Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Focus)),[Advisor Name]))

/ count(aggr(Count({$<[Focus]-={1,2,29,30,31,40},[Est Group]={""},[Prob Group]={""}>}DISTINCT Coach),[Advisor Name]))

Many thanks for your assistance 🙂

Capture2.JPG.jpg