Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
GuyIncognito
Contributor II
Contributor II

Chart for recurring Salespeople v2

Hi assembled geniuses,

I posted a question a while ago but it was incomplete (my bad).

I need an expression for the attached graph and table which will:

  • Sum the sales by year and by salesperson BUT
  • exclude completely any salesperson that does not have exactly one record in each sales year
  • very ideally, this should be future-proofed so that when we have 2020 data, the expression does not need amending

For clarity, the graph and table would exclude:

Sue and Fred (more than one record in at least one year) and Mikey and Guy (no records in at least one of the sales years), 

leaving just John (one record in every sales year).

 

Go!

 

Thanks in advance 🙂

 

1 Solution

Accepted Solutions
GuyIncognito
Contributor II
Contributor II
Author

Thanks,

Solution found as follows:

 

If(Aggr(count (Year),Name,Year)=1 and (count(Distinct TOTAL Year) = count(total <Name>Year)) ,Sum(Sales))

View solution in original post

2 Replies
Brett_Bleess
Former Employee
Former Employee

About the best I can do are the following links that may help, and the last link is the base URL to the Design Blog area in case you want to do some further searching on your own, there are hundreds of how-to posts from our experts in this area...

https://community.qlik.com/t5/Qlik-Design-Blog/Use-Aggregation-Functions/ba-p/1475833

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

My post will kick things back up, someone else may provide something direct at that point, but cannot promise.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
GuyIncognito
Contributor II
Contributor II
Author

Thanks,

Solution found as follows:

 

If(Aggr(count (Year),Name,Year)=1 and (count(Distinct TOTAL Year) = count(total <Name>Year)) ,Sum(Sales))