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

# Calculated field based off last X months

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:

Incorrect format:

Cheers,

Mikey

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

Mikey,

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!