3 Replies Latest reply on Aug 27, 2018 9:12 AM by Susan Miller

# Pulling dates first time running sum of sales surpass certain number by Reps?

I have a table displaying sales by individual sales reps that looks something like this:

 Rep ID Order Date Sales 1 8/1/2018 698 2 8/2/2018 865 3 8/3/2018 984 2 8/4/2018 1266 2 8/5/2018 437 3 8/6/2018 1690 2 8/7/2018 805 1 8/8/2018 497 1 8/9/2018 1664

What I'm trying to do is pull the first time each Rep's sales surpass a certain number - so for example, if I want to see the date each Rep's sales surpass \$1,000, each Rep would show the following dates:

 Rep ID Date sales surpassed 1 8/8/2018 2 8/4/2018 3 8/6/2018

Can anyone help me out? I'm totally stuck!

EDIT: I'm looking for a running sum of sales by Rep - so for example Rep 1 should show a date of 8/8 (NOT 8/9) since the sum of his sales on 8/1 and 8/8 total to 1,195.

• ###### 1. Re: Pulling dates first time sales surpass certain number by Reps?

Determine the number that you are looking for and created a calculated field like this.

IF[Sales]=??? THEN min([Order Date]) END

• ###### 2. Re: Pulling dates first time sales surpass certain number by Reps?

Hi Susan,

You can try below logic:

new date=  {FIXED [Rep ID],[Order Date]:max(if ([Sales])>1000 then [Order Date] end)}

• ###### 3. Re: Pulling dates first time sales surpass certain number by Reps?

I should have clarified on my original post - I need to do a running sum of each stylists total sales that surpass a total number. So for example, Rep 1's total sales surpass \$1000 on 8/8, so that is the date I want displayed (not on 8/9 when his singular sales surpass \$1000).

 Rep ID Order Date Sales 1 8/1/2018 698 2 8/2/2018 865 3 8/3/2018 984 2 8/4/2018 1266 2 8/5/2018 437 3 8/6/2018 1690 2 8/7/2018 805 1 8/8/2018 497 1 8/9/2018 1664