Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Announcements
This forum is for questions and information about how to use the Qlik Community.
Please do not post product related questions here.
Select the correct forum from: Qlik Product Forums
Tee_dubs
New Contributor II

Sum of Distinct values of another field

I have a table

TASKNUMBERACCESSACCESSKEYMH
A   
A824A-8240.025
B824B-8240.025
B195B-1950.02
C195C-1950.02

 

I want to sum the values of MH only once for the Distinct values of 'ACCESS' 

This is what I have so far.

sum(Aggr(Count(distinct ACCESS),ACC_MH)),ACC_MH) 

It driving me a bit crazy. The solution to this will come in very handy for my whole app. 

I don't think I can avoid AGGR here, however, Performance is becoming a concern so a Set Analysis solution is preferred.

4 Replies

Re: Sum of Distinct values of another field

Set analysis won't work here... but you can try this

Sum(Aggr(Sum(MH), ACCESSKEY))

Alternatively, you can take ACCESS and MH from your table and add it to another table in the backend so that only the distinct combination of ACCESS and MH exists and will join to your main table on ACCESS... if you do that, you will be able to just do Sum(MH)....

Tee_dubs
New Contributor II

Re: Sum of Distinct values of another field

That will give me the Sum(MH) in the example above the answer will be 0.09 the sum of all MH aggregated by the distinct key Accesskey.
What I want is 0.045 or the sum of the 2 distinct accesses 824 and 195.
Perhaps the best idea would be to remove the MH column from the source and create a new Table with only the ACCESS and MH...
Highlighted

Re: Sum of Distinct values of another field

My so very bad... I think I am ready for the weekend... try this

Sum(Aggr(Sum(DISTINCT MH), ACCESSKEY))
Tee_dubs
New Contributor II

Re: Sum of Distinct values of another field

Don't feel bad. You've been a great help with my last few posts. Have an awesome weekend!! I'll give that a try but i think changing the back end as suggested is the way to go. Smiley Wink
Community Browser