topic Chart for Consistent Salespeople in QlikView

Chart for Consistent Salespeople

GuyIncognito — Mon, 11 Nov 2019 11:58:20 GMT

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:

Re: Chart for Consistent Salespeople

tresB — Mon, 11 Nov 2019 12:08:32 GMT

Try:

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

Re: Chart for Consistent Salespeople

GuyIncognito — Mon, 11 Nov 2019 12:37:55 GMT

Works a treat - thanks so much!

Re: Chart for Consistent Salespeople

GuyIncognito — Mon, 11 Nov 2019 16:09:14 GMT

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.