Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for
Search instead for
Did you mean:
Creator

## Sum Distinct Values in Table Total

Hello,

I have a question that i cant seem to solve.

I have the following table:

 Region Coverage Sales Employee Targets East user1 1,350,000 East user2 1,350,000 East user3 1,350,000 East user4 1,350,000 Total 5,400,000

Since, the region is the same and the targets are the same, can we have the total to be 1,350,000 since all targets in the regions are the same?

Thank you in advance.

Waleed

1 Solution

Accepted Solutions
MVP

May be try this

``````If([Region Coverage] = 'East',
Aggr(NODISTINCT Sum(DISTINCT Aggr(Sum(Target), [Region Coverage], [User])), [Region Coverage])),
Sum(Target)
)``````
23 Replies

Try this out: Sum(Aggr(Sum(Targets),[Region Coverage]))

Or

Sum(total <[Region Coverage]> Targets)

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator
Author

Hi Thiago,

It doesnt work. It gives 5400000 to one user and makes rest 0. Also, i do have other regions in this which have targets of their own and not same at all.

Thanks

Check: Sum(Aggr(Sum(Targets),distinct [Region Coverage]))

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator
Author

Hello,

This turns it all 0.

Thanks

Are you able to share a mockup of your data?

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator
Author

Hello thiago,

Unfortunately, the data is confidential and in a vast amount. all other regions are okay. But East we have it set up differently and we need to add it only once.

This is the following table with all users:

 Region Coverage Users Target Total 7,300,000 Central Total 1,250,000 User1 400,000 User2 25,000 User3 75,000 User4 300,000 User5 200,000 User6 0 User7 250,000 East Total 5,400,000 User8 1,350,000 User9 1,350,000 User10 1,350,000 User11 1,350,000 West Total 650,000 User12 350,000 User13 100,000 User14 200,000

Thanks.

Contributor

Hi Thiago,

Try with -> sum({<Users={'Total'}>} Target)

However, I recommended normalized your data

Here it is:

Sum(total <[Region Coverage]> distinct Targets)

The output:

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator
Author

Hi Thiago,

Is it possible you could try this with the larger data set i provided above? I have tried the solution above and it is giving me random numbers again. Please note that i have other conditions in my set analysis as well. Also, Shouldnt the set analysis have {} around the <> symbols? so it becomes

sum(total {<[region Coverage]>} distinct Targets)

Please let me know.

Thank you

Tags
Community Browser