# 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
Did you mean:
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)

 employee item valueperitem valueforemployee quarter A p1 2295 2295 Q1-2019 A p2 0 2295 Q1-2019 A p3 0 2295 Q1-2019 A p1 4140 4140 Q2-2019 A p1 3168 3168 Q3-2019 A p1 3168 3168 Q4-2019 A p1 5800 5800 Q1-2020 A p1 4000 4000 Q2-2020 B p1 1314 1314 Q1-2019 B p2 0 1314 Q1-2019 B p4 2328 2328 Q2-2019 B p4 4320 4320 Q3-2019 B p4 1824 1824 Q4-2019 B p4 3000 3000 Q1-2020 B p4 3000 3000 Q2-2020 total - 38357

thanks for helping in advance

1 Solution

Accepted Solutions
MVP

Try:

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

6 Replies
MVP

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

sum(valueforemployee

Contributor
Author

That will include duplicates in the sum.

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

MVP

Are you keeping the duplicate values from same item?

Contributor
Author

yes. if they belong to different quarter.

My output should be like below one

 employee valueforemployee quarter A 2295 Q1-2019 A 4140 Q2-2019 A 3168 Q3-2019 A 3168 Q4-2019 A 5800 Q1-2020 A 4000 Q2-2020 B 1314 Q1-2019 B 2328 Q2-2019 B 4320 Q3-2019 B 1824 Q4-2019 B 3000 Q1-2020 B 3000 Q2-2020
MVP

Try:

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

Contributor
Author

Thank you. this worked for me.

Tags
Community Browser