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

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
adamdavi3s
Master
Master

Complex aggr (*I think*)

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

 

ClientCodeEmployeeCodeEmployee Visitstotal client visitstotal number employeesresult
1a4885-0.02%
1b88850.61%
1c68850.24%
1d1885-0.22%
1e6988561.96%
1Total8888562.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:

ClientCodeResult
162.57%
212.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!

1 Solution

Accepted Solutions
adamdavi3s
Master
Master
Author

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

View solution in original post

5 Replies
Anonymous
Not applicable

Maybe something like the attached

Anonymous
Not applicable

I pasted you table into an excel;

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

.qvw attached

Anonymous
Not applicable

Or if you need to omit the Total row;

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

adamdavi3s
Master
Master
Author

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

adamdavi3s
Master
Master
Author

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