topic Re: Complex aggr (I think) in QlikView

Complex aggr (I think)

adamdavi3s — Wed, 25 Nov 2020 16:16:04 GMT

I can't figure this formula out and I am going round in circles so I put myself at your mercy.

Consider the following table

ClientCode	EmployeeCode	Employee Visits	total client visits	total number employees	result
1	a	4	88	5	-0.02%
1	b	8	88	5	0.61%
1	c	6	88	5	0.24%
1	d	1	88	5	-0.22%
1	e	69	88	5	61.96%
1	Total	88	88	5	62.57%

I Just need to calculate that one figure in the bottom right which is the sum of the percentages above it, note it can be expected that there will be a number of ClientCodes and the result should be calculated for each inidividually

End result should be something like:

ClientCode	Result
1	62.57%
2	12.58%

The formula are quite simple, given the dimensions ClientCode and EmployeeCode

Employee visits = count({<whole bunch of set>} VisitCode)

Total Client visits = count({<whole bunch of set>} TOTAL <ClientCode> VisitCode)

Total Number Employees = count({<whole bunch of set>} DISTINCT TOTAL <ClientCode> EmployeeCode)

The result column is a bit more complex:

=(pow([Employee Visits],2)-([total client visits]/[total number employees]))/(pow([total client visits],2)- [total client visits])

NOTE! It needs to be calculated at the line level and then added up, NOT just the total level, which is the bit that has me foxed!

Re: Complex aggr (I think)

Anonymous — Fri, 17 Feb 2017 14:46:42 GMT

Maybe something like the attached

Re: Complex aggr (I think)

Anonymous — Fri, 17 Feb 2017 14:46:54 GMT

I pasted you table into an excel;

=Aggr(sum({<ClientCode={1}>}result),ClientCode)

.qvw attached

Re: Complex aggr (I think)

Anonymous — Fri, 17 Feb 2017 14:50:16 GMT

Or if you need to omit the Total row;

=Aggr(sum({<ClientCode={1},EmployeeCode-={'Total'}>}result),ClientCode)

Re: Complex aggr (I think)

adamdavi3s — Fri, 17 Feb 2017 14:50:58 GMT

Bill,

If I take all the other dimensions our yours doesn't work

The actual answer is (after a momentary flash of inspiration)

=sum(aggr((pow(count(VisitCode),2)-(count(total <ClientCode> VisitCode)/count(DISTINCT TOTAL <ClientCode> EmployeeCode)))/(pow(sum(total <ClientCode> VisitCode),2)-count(total <ClientCode> VisitCode)),ClientCode,EmployeeCode))

Re: Complex aggr (I think)

adamdavi3s — Fri, 17 Feb 2017 14:51:49 GMT

Gareth, unfortunately result is a generated row and not one I have available to me

topic Re: Complex aggr (*I think*) in QlikView

Complex aggr (*I think*)

Re: Complex aggr (*I think*)

Re: Complex aggr (*I think*)

Re: Complex aggr (*I think*)

Re: Complex aggr (*I think*)

Re: Complex aggr (*I think*)

topic Re: Complex aggr (I think) in QlikView

Complex aggr (I think)

Re: Complex aggr (I think)

Re: Complex aggr (I think)

Re: Complex aggr (I think)

Re: Complex aggr (I think)

Re: Complex aggr (I think)