Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
srivalli2345
Contributor
Contributor

calculate sum of the value if there are duplicates

Hi all,

I am trying to show a KPI with total values based on below data. However , there are duplicates in the column and it is not giving me exact output. I want the sum to be 38357 using valueforemployee instead of valueperitem column. I used below functions

sum(distinct valueforemployee

Aggr(sum(valueforemployee),employee)

 

employeeitemvalueperitemvalueforemployeequarter
Ap122952295Q1-2019
Ap202295Q1-2019
Ap302295Q1-2019
Ap141404140Q2-2019
Ap131683168Q3-2019
Ap131683168Q4-2019
Ap158005800Q1-2020
Ap140004000Q2-2020
Bp113141314Q1-2019
Bp201314Q1-2019
Bp423282328Q2-2019
Bp443204320Q3-2019
Bp418241824Q4-2019
Bp430003000Q1-2020
Bp430003000Q2-2020
  total - 38357  
     
 

thanks for helping in advance

 

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try:

=Sum(Aggr(Sum(DISTINCT valueforemployee), employee, quarter))

View solution in original post

6 Replies
tresesco
MVP
MVP

May be just remove distinct from sum(), like:

sum(valueforemployee

srivalli2345
Contributor
Contributor
Author

That will include duplicates in the sum. 

actually valueforemployee column is the total sum of valueforitem for a particular employee

 

tresesco
MVP
MVP

Are you keeping the duplicate values from same item?

srivalli2345
Contributor
Contributor
Author

yes. if they belong to different quarter.

My output should be like below one

employeevalueforemployeequarter
A2295Q1-2019
A4140Q2-2019
A3168Q3-2019
A3168Q4-2019
A5800Q1-2020
A4000Q2-2020
B1314Q1-2019
B2328Q2-2019
B4320Q3-2019
B1824Q4-2019
B3000Q1-2020
B3000Q2-2020
tresesco
MVP
MVP

Try:

=Sum(Aggr(Sum(DISTINCT valueforemployee), employee, quarter))

srivalli2345
Contributor
Contributor
Author

Thank you. this worked for me.