1 Reply Latest reply on Aug 31, 2016 4:19 PM by Santiago Sanchez

    Calculated field based off last X months

    Mikey Michaels

      Hello All,

      On the attached, I want to present only those salespeople who had >7 Records AND >30% EASy Utilization over the last N months (not sure how to create this right now).


      I realize I could just add quick filters for these data points, however, I want to always show the line chart as a solid line over the 13 months. For instance, if I set the last N months filter to 3 months,  I still want to see the salespersons' order count over 13 months, but only want to present those salespeople who met the >7 records AND >30% EASy Utilization... Is this something a Set could answer?


      Correct format with set:


      Correct format.PNG

      Incorrect format:

      incorrect format.PNG


      Thanks for your time.




        • 1. Re: Calculated field based off last X months
          Santiago Sanchez



          I'm not sure I understand the logic behind Easy Utilization, but I believe you can solve this using an LOD calculation. In the attached workbook there are 5 calculated fields:


          1. Records in last N Months: number of 'Easy' records only, in the last 3 months.

          2. Minimum Records LOD: for each sales person, return the total number of easy records in the last 3 months. This is an LOD and uses the calculation just above.

          3: Is Easy in last N Months: for 'Easy' records only, in the last 3 months, return [Order Value (USD)]. (Should this be a transaction ID instead?)

          4: Easy Utilization LOD: Attempt to re-create 'Easy Utilization'. Get the COUNT of [Is Easy in last N Months] for each sales person, divided by the COUNT of [Is Easy in last N Months] for all the data set. This is an LOD too.


          With those, you get the last calculation, [SalesPerson Filter], that evaluates your conditions. With the logic above I get numbers that are nowhere near your thresholds, so I've changed them and I know this won't likely be the final solution. However, this hopefully gives you and idea on how you can use LODs to tackle the problem!