Chart for Consistent Salespeople

[GuyIncognito]

Hi,

I am struggling to produce a chart which displays results only where individuals are present in all years.

In the example, I have a number of salespeople and two years of data. Some are new salespeople, and some have left.

I need to create a graph that:

• only displays sales results for consistent people present in all years of sales (ie excluding starters and leavers in the period). So I'm trying to compare 'apples with apples'.
• Sums sales by year. So expectation is 2018 = 17 and 2019 = 29

Attached is my attempt using a variable to count the number of sales years (vCountYear) and using the dreaded AGGR function in the chart expression - (mine doesn't work!)

Help/thanks!

Expectation:

[tresesco · Accepted solution]

Try:

=Sum({<Name={"=Count(Name)=Count({1} Distinct total Year)"}>}Sales)

[GuyIncognito]

Works a treat - thanks so much!

[GuyIncognito]

Hi - This does work for the dummy data however have hit a snag - there are a few cases where the salesperson has multiple records in one or both years (this is just a dummy example, there are sound reasons why this is so).

Can the expression be amended to check that there is one and only one entry per year, and to disregard any cases where this isn't true?

Thanks in advance.