Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
waleeed_mahmood
Creator
Creator

Sum Distinct Values in Table Total

Hello,

I have a question that i cant seem to solve.

I have the following table:

Region CoverageSales EmployeeTargets
Eastuser11,350,000
Eastuser21,350,000
Eastuser31,350,000
Eastuser41,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
sunny_talwar

May be try this

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

View solution in original post

23 Replies
thiago_justen

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

Or

Sum(total <[Region Coverage]> Targets)

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
waleeed_mahmood
Creator
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

thiago_justen

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

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

Hello,

 

This turns it all 0. 

 

Thanks

thiago_justen

Are you able to share a mockup of your data?

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
waleeed_mahmood
Creator
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 CoverageUsersTarget
Total 7,300,000
CentralTotal1,250,000
User1400,000
User225,000
User375,000
User4300,000
User5200,000
User60
User7250,000
EastTotal5,400,000
User81,350,000
User91,350,000
User101,350,000
User111,350,000
WestTotal650,000
User12350,000
User13100,000
User14200,000

 

Thanks.

Jflori4n
Contributor
Contributor

Hi Thiago,

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

However, I recommended normalized your data

thiago_justen

Here it is:

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

 

The output:

Captura de tela 2021-09-22 165725.png

 

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
waleeed_mahmood
Creator
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