Qlik Community

App Development

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

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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

23 Replies
thiago_justen

This is the output I have:

thiago_justen_0-1632416902493.png

 

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

Hi,

Do you mind sharing the QVF file?

Thank you

thiago_justen

Here is the script I've used:

 

Temp:
LOAD * inline [
Region Coverage, Users, Target
Central,User1, 400000
Central,User2, 25000
Central,User3, 75000
Central,User4, 300000
Central,User5, 200000
Central,User6, 0
Central,User7, 250000
East,User8, 1350000
East,User9, 1350000
East,User10, 1350000
East,User11, 1350000
West,User12, 350000
West,User13, 100000
West,User14, 200000
];

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

You might be able to use this

Aggr(NODISTINCT Sum(DISTINCT Target),  [Region Coverage]))
thiago_justen

Great @sunny_talwar!!

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

Hello Sunny, Thiago,

Using your script and expressions, this is what i get.

waleeed_mahmood_0-1632419229878.png

 

Except East, other regions have their own targets that should appear. Only east is the exception.

Thank you.

sunny_talwar

I am not entirely sure what you need, but may be this

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

 

waleeed_mahmood
Creator
Creator
Author

Hi Sunny,

Ill try your expression but please see below for desired result:

waleeed_mahmood_0-1632419784706.png

Thank you

thiago_justen

If(Dimensionality()=0,Sum(Targets),Sum(total <[Region Coverage]> distinct Targets))

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

I am guessing Current Result uses Sum(Target)... if it does, then I think this should work

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