I would need help on the following:
I would like to map 2016 turnover client in a mapp filtered by Sales rep.
Size should be according to 2016 Turnover value but color should be according to achievement of 2015 yearly Sales rep visit done vs Sales rep goals. (see my enclosed file for more details)
Here are my relevant data:
Year (2015 or 2016)
# Visists per year (visits that sales rep. performed during the year)
This is how I did it to calculate visit done vs Sales rep goals:
Calculated field: Visit Goals:
IF [Turnover] >=900000 and [Year]=2015 then 5
ELSEIF [Turnover] >=100000 and [Year]=2015 then 4
ELSEIF [Year]=2015 then 2
Calculated field: Visit Achievement:
[# Visists per year] >=[Visit Goals]
I use then these fields in order to colorize my map.
But....I have 2 issues where I need help:
I would like to modify the calculated field: visit goal in order to follow the following rule by sales rep:
The highly performing client (order turnover 2015 and pick the first ones that represent 30% in term of count of a Sales rep total portfollio ) should be visited 5 times
The medium performing client (pick the clients that represent the following 30% in term of count of a Sales rep total portfollio) should be visited 4 times
The other clients should be visited 2 times
In my enclosed example lets take Sale rep: CAMUS, Albert
He had 14 accounts in 2015 so I will consider hihgly performing Client being the first 4 customers (30% of the count of a sales rep portfollio),
So client with goal being 5 visits should be:
- Bouchon lyonnais, Lyonnais, Rhône
- Le Brebis du pays de Grasse, Alpes-Maritimes
- Avalin Val d'Isère Savoie
- Brousse du Rove, Bouches-du-Rhône, Provence-Alpes-Côte d'Azur
So client with goal being 4 visits should be:
-Bleu de Loudes, Auvergne
- Beaufort, Savoie essentiellement, AOC 1993
- Boulette de la Pierre-qui-Vire, Bourgogne
- Bondon, Normandie et Poitou-Charentes
Other should have a goal of 2 visits.
Any idead for my calculated field? I need this to apply to any of my sales rep. whatever the number of their client.
I need to see 2016 performance in the map but color need to be based on 2015 performance. (Size= turnover 2015 / color = Visit achievement based on 2015 turnover)
in my enclosed file either I select 2016 and I do not see my color for calculated fied visit achievement or I select 2015 I see the calculated field but not the 2016 turnover.
Any idea on this one I think it is a matter of LOD but I am quite bad at this.
Thanks a lot for your help,
Mockup_VisitReport.twbx 48.1 KB