Days Since Last Instance

angela2018

I need to build a beast mode that gives me the days since the last time a sales rep transacted. My dataset includes several transactions by the same rep but I only want one line per sales rep and however many days it has been since their most recent sale. Does anyone have experience with this?

 

--I've built a beast mode to give me the date difference for today vs. the date of sale. I just need it to ONLY return the most recent sale data rather than one line for each of the rep's sales. 

Chips

Could you use a "Max" aggregation on the date of sale?

 

DateDiff(Max(Date of Sale),Current Date)?

angela2018

So that is basically giving me the same results as a different beast mode that I have in this set (which tells me how many days it has been since the sale). But the issue is that each transaction currently has its own line, which means sales reps have like 12 lines each. I only want one line per rep stating the # of days since their most recent sale rather than how many days it has been since each of their sales.  

Chips

So the Max is aggregating at the lowest common denominator shown which in your case is Order Number AND Rep. 

 

If you remove Order Number from the table (and any other Order identification), it should work. 

 

If you have a Table Card that is simply the following two columns, it should work.

 

Column 1 Rep Name

Column 2 DateDiff(Max(Date of Sale),Current Date)

 

 

angela2018

That gave me the sum of all days between today and A, B, C, D, E transactions (for example, someone who transacted as recently as last week came out to 246 days due to it aggregating all of his transactions). 

ST_Superman

I've run into a similar issue when dealing with max and min aggregates with date fields.  Try changing the order a bit:

 

Column 1 Rep Name

Column 2 MIN(DateDiff(`Date of Sale`,Current Date)