2 Replies Latest reply on Dec 12, 2016 6:53 PM by Shinichiro Murakami

    How to do a conditional statement based on previous year ?

    Timothé Le Vigouroux

      Hi Everybody,

       

      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:

      Client name

      Year (2015 or 2016)

      Turnover

      Postal codes

      Sales rep.

      # 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

      END

       

      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:

       

      Issue 1:

      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.

       

      Issue 2:

      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,

       

      Timothé

        • 1. Re: How to do a conditional statement based on previous year ?
          Stephen Rizzo

          For your second issue, the problem is that [Visit Goals] returns null for [Year] = 2016. You should be able to fix both problems by changing your calculated formula to the following table calculation for [Visit Goals]

           

          IF RANK(MAX([# Visits per year in 2015]), 'desc') / WINDOW_COUNT(ATTR([Client Name])) <= .3 THEN 5

          ELSEIF RANK(MAX([# Visits per year in 2015]), 'desc') / WINDOW_COUNT(ATTR([Client Name])) <= .6 THEN 4

          ELSE 2

          END

           

          With [# Visits per year in 2015] defined as

           

          {EXCLUDE [Year] : MAX((IF [Year] = 2015 THEN 1 ELSE 0 END) * [# Visits per year])}

          1 of 1 people found this helpful
          • 2. Re: How to do a conditional statement based on previous year ?
            Shinichiro Murakami

            Hi Timothé

             

             

            I'm not sure I understand your request correctly, but anyways here is my approach.

             

            [# Visists per 2015]

            {fixed [Sales Rep],[Postal Code]:sum(if [Year]=2015 then[# Visists per year] end)}

             

            [Sales Rep Rank]

            rank_unique(attr([Yr 2015 Tourn Over]),'desc')

             

            [Visit Goals]

            IF [Sales Rep Rank]<= int(attr({fixed :countd([Sales Rep])})*0.3)

            then 5

            elseif [Sales Rep Rank] <= int(attr({fixed :countd([Sales Rep])})*0.6)

            then 4

            else 2

            end

             

            [Visit Achievement]

            attr([# Visists per 2015]) >=[Visit Goals]

             

             

             

            Thanks,

            Shin

            1 of 1 people found this helpful