topic Re: Aggregation by Name in QlikView

Aggregation by Name

Fri, 21 Mar 2014 11:48:59 GMT

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

Re: Aggregation by Name

Gysbert_Wassenaar — Fri, 21 Mar 2014 12:29:24 GMT

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

Re: Aggregation by Name

Fri, 21 Mar 2014 13:16:00 GMT

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.

Re: Aggregation by Name

Gysbert_Wassenaar — Fri, 21 Mar 2014 13:50:25 GMT

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

Re: Aggregation by Name

Fri, 21 Mar 2014 14:17:43 GMT

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

Re: Aggregation by Name

Fri, 21 Mar 2014 15:01:26 GMT

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 🙂