1 Reply Latest reply on Aug 1, 2017 2:58 PM by Deepak Rai

    Calculation: Sales Brand Performance of Overall Portfolio  - multiple criteria

    Jorge Palacio

      Hi All, I'm attempting to write a calculation that would automate some work that is being done and save a ton of time!

      Here is a background of the issue.


      Here at my job we accrue sales for potential sales payouts based on multiple performance criteria.


      We have a Target 1 Quota that is indiscriminate of the brand. -Once reached they get paid 1% of their annual Sales We have a Target 2 Quota that is based on brand performance. -We have quotas for 9 Brands however, not everyone is given a quota for that particular brand.

      My first calculation to determine Target 1 Potential 1% Payout is


      "IF (([% Quota YTD]>=1) THEN ((SUM([YTD Sales])*0.01)"


      This works fine but the tricky part is when I'm trying to create Target 2 Potential Payout.


      The logic is the following: There are 7 Brands that are given a quota Each individual may or may not be given a quota for all brands.


      You may have an individual with 2 or 3 brand quotas, despite have sales information for those brands not given a quota.


      My current Calculation contains two different dimensions.


      Brand YTD Sales "IF [Brand-New3] = 'Name1' OR

      [Brand-New3] = 'Name2' OR

      [Brand-New3] = 'Name3' OR

      [Brand-New3] = 'Name4' OR

      [Brand-New3] = 'Name5' OR

      [Brand-New3] = 'Name6' OR

      [Brand-New3] = 'Name7' OR

      [Brand-New3] = 'Name7' OR

      [Brand-New3] = 'Name8' OR

      [Brand-New3] = 'Name9' THEN ([YTD Sales]) END"


      This works great; however, those individuals that have sold Brand 'Name7' but who were not given a quota still get summed up! Would a window sum be appropriate in this scenario?


      Ok and finally the Target 2 Potential Payout:

      "IF ([%Quota YTD Level 2]>1) THEN ((([% Brand Quota YTD- 2017]0.01)([Potential 1% Payout])) END"


      This seems like it should not be as difficult as it seems. I have thought of many other ways to go about this.


      Create calculation for each individual Brand for example:

      Brand Name1

      "IF [Brand-New3] = 'Name1' THEN [YTD Sales]) END"


      The only issue with this again is it gives me sales for brands that individuals do not have quotas for.


      I'm thinking a LOD expression?


      Anyway I hope I explained myself clearly, I could really use the help!